Arrests

arrests <- read.csv("../../export/output/arrests.csv.gz")
arrests <- arrests %>% filter(aor != "HQ") %>%
  mutate(apprehension_date2 = as.Date(apprehension_date, format = "%m/%d/%Y"),
                              apprehension_year = format(apprehension_date2, "%Y"))
arrests %>% select(1:8) %>% head() %>% kable(caption = "`arrests` dataframe")
arrests dataframe
aor apprehension_date apprehension_method apprehension_landmark operation processing_disposition citizenship gender
SPM 10/1/2015 Probation and Parole OMAHA NE NON-FUGITIVE ARREST REINSTATEMENT OF DEPORT ORDER I-871 MEXICO Male
CHI 10/1/2015 Non-Custodial Arrest CHI GENERAL AREA, NON-SPECIFIC Bag and Baggage GUATEMALA Male
LOS 10/1/2015 CAP State Incarceration CALIFORNIA MENS COLONY WEST Other LAOS Male
HOU 10/1/2015 CAP State Incarceration TEXAS DEPT OF CRIMINAL JUSTICE, WALKER CO REINSTATEMENT OF DEPORT ORDER I-871 MEXICO Male
SNA 10/1/2015 CAP Federal Incarceration HOUSTON ERO CONTRACT DET FACILITY Other MEXICO Male
CHI 10/1/2015 CAP Local Incarceration O’FALLON POLICE DEPARTMENT, MISSOURI Warrant of Arrest/Notice to Appear GUATEMALA Male 
arrests <- arrests %>% mutate(fy = case_when(apprehension_date2 < "2016-10-01" ~ 2016,
                                                             apprehension_date2 < "2017-10-01" ~ 2017,
                                                             apprehension_date2 < "2018-10-01" ~ 2018,
                                                             apprehension_date2 < "2019-10-01" ~ 2019),
                              apprehension_month = format(apprehension_date2, "%m"),
                              apprehension_month_y = format(apprehension_date2, "%Y-%m"),
                              month = month(apprehension_date2))
arrests %>% 
  group_by(fy) %>%
  summarize(total = n()) %>%
  ggplot(aes(fy, total, group = 1)) + 
  geom_point() +
  geom_line() +
  labs(x = "Apprehension Year (Fiscal)",
       y = "Total Yearly Apprehension",
       title = "Total Arrests Across AOR FY 2016-2019") +
  theme_few()

arrests %>% 
  group_by(month, fy) %>%
  summarize(total = n()) %>%
  mutate(Year = factor(fy)) %>%
  mutate(Month = factor(month,
                        levels = c(10:12, 1:9),
                        labels = c(month.abb[10:12], month.abb[1:9]))) %>%
  ggplot(aes(x = Month, y = total, color = Year, group = Year)) +
  geom_line() +
  geom_point() +
  scale_color_brewer(palette="Dark2") +
  theme_few() +
  theme(legend.position="bottom") +
  labs(color = "FY",
       title = "Monthly Arrests Across AOR FY 2016-2019",
       y = "Total Arrests")

arrests %>% 
  group_by(fy, aor) %>%
  summarize(total = n()) %>%
  ggplot(aes(as.numeric(fy), total, group = aor)) + 
  geom_line(aes(col = aor)) +
  scale_x_continuous(limits = c(2016, 2019)) +
  labs(x = "Apprehension Year (Fiscal)",
       y = "Yearly Apprehension by AOR",
       title = "Total Arrests by AOR FY 2016-2019") +
  facet_wrap(~aor) +
  theme_few() +
  theme(legend.position = "none",
        axis.text.x = element_text(angle = 45))

#mutate(quarter = quarter(date, with_year = TRUE, fiscal_start = 10), # Here we extract FY on the fly and lose date functionality
         #fy = stringr::str_sub(quarter, 1, 4)) %>% 
  #group_by(fy)

yearly_change <- arrests %>% 
  group_by(aor, fy) %>%
  summarize(total = n()) %>%
  pivot_wider(names_from = fy, values_from = total) 

colnames(yearly_change) <- c("aor", "y_2016", "y_2017", "y_2018", "y_2019")

yearly_change <- yearly_change %>% rowwise() %>% 
  mutate(pct_change_17 = round(((y_2017-y_2016)/y_2016) * 100, 2),
         pct_change_18 = round(((y_2018-y_2017)/y_2017) * 100, 2),
         pct_change_19 = round(((y_2019-y_2018)/y_2018) * 100, 2))

yearly_change %>% kable(caption = "Yearly % change in arrests by AOR")
Yearly % change in arrests by AOR
aor y_2016 y_2017 y_2018 y_2019 pct_change_17 pct_change_18 pct_change_19
ATL 8866 13551 15189 12268 52.84 12.09 -19.23
BAL 1239 1666 1703 1330 34.46 2.22 -21.90
BOS 1858 2834 2908 2266 52.53 2.61 -22.08
BUF 1173 1494 1582 1206 27.37 5.89 -23.77
CHI 7055 8604 9241 7769 21.96 7.40 -15.93
DAL 9634 16520 17644 15571 71.48 6.80 -11.75
DEN 2284 2746 2755 2215 20.23 0.33 -19.60
DET 2241 3409 3604 3094 52.12 5.72 -14.15
ELP 1611 1892 2159 2103 17.44 14.11 -2.59
HOU 12896 13565 14333 10761 5.19 5.66 -24.92
LOS 7651 8419 7854 6144 10.04 -6.71 -21.77
MIA 3524 6192 8474 8695 75.71 36.85 2.61
NEW 2247 3189 3409 2654 41.92 6.90 -22.15
NOL 5174 7968 10270 9075 54.00 28.89 -11.64
NYC 1847 2576 3476 2281 39.47 34.94 -34.38
PHI 3672 4938 5143 3752 34.48 4.15 -27.05
PHO 5370 6457 7162 5420 20.24 10.92 -24.32
SEA 2698 3376 3067 2328 25.13 -9.15 -24.10
SFR 6651 7231 6210 4724 8.72 -14.12 -23.93
SLC 4638 5177 5754 5014 11.62 11.15 -12.86
SNA 8425 8510 10749 10968 1.01 26.31 2.04
SND 3683 4551 6000 2107 23.57 31.84 -64.88
SPM 2500 4175 4658 4005 67.00 11.57 -14.02
WAS 2883 4163 4359 3734 44.40 4.71 -14.34 
yearly_change %>% select(aor, 6:8) %>%
  pivot_longer(cols = pct_change_17:pct_change_19, names_to = "Type", values_to = "Value") %>%
  ggplot(aes(x = aor, y = Value)) + geom_bar(stat = "identity") + facet_wrap(~Type) + 
  scale_y_continuous(labels = scales::percent_format(scale = 1)) +
  coord_flip() +
  labs(title = "Yearly % change in arrests by AOR") +
  theme_few()

Consistent trend across all AORs: upward trend in arrests number from 2016-2018 and then a drop in 2019.

# Monthly data
arrests_2016 <- arrests %>% filter(fy == 2016) %>%
  group_by(aor, apprehension_month) %>%
  summarize(total = n()) %>%
  pivot_wider(names_from = apprehension_month, values_from = total) 

colnames(arrests_2016) <- c("aor", "jan", "feb", "mar", "apr", "may", "jun", "jul", "aug", "sep", "oct", "nov", "dec")

arrests_2016 <- arrests_2016 %>% rowwise() %>% 
  mutate(pct_change_02 = round(((feb-jan)/jan) * 100, 2),
         pct_change_03 = round(((mar-feb)/feb) * 100, 2),
         pct_change_04 = round(((apr-mar)/mar) * 100, 2),
         pct_change_05 = round(((may-apr)/apr) * 100, 2),
         pct_change_06 = round(((jun-may)/may) * 100, 2),
         pct_change_07 = round(((jul-jun)/jun) * 100, 2),
         pct_change_08 = round(((aug-jul)/jul) * 100, 2),
         pct_change_09 = round(((sep-aug)/aug) * 100, 2),
         pct_change_10 = round(((oct-sep)/sep) * 100, 2),
         pct_change_11 = round(((nov-oct)/oct) * 100, 2),
         pct_change_12 = round(((dec-nov)/nov) * 100, 2))

arrests_2016 %>% select(1, 14:24) %>% kable(caption = "Monthly % change in arrests by AOR in FY 2016")
Monthly % change in arrests by AOR in FY 2016
aor pct_change_02 pct_change_03 pct_change_04 pct_change_05 pct_change_06 pct_change_07 pct_change_08 pct_change_09 pct_change_10 pct_change_11 pct_change_12
ATL 5.71 3.37 -14.75 -5.21 23.42 -20.94 20.03 20.41 4.58 -16.32 -2.75
BAL 8.97 72.94 -14.97 -16.00 -17.14 6.90 60.22 -36.24 -14.74 32.10 -18.69
BOS 8.94 16.42 39.10 -27.65 4.46 -12.80 44.76 -28.99 -28.57 52.38 -9.38
BUF -3.57 45.68 -3.39 -26.32 20.24 -12.87 42.05 -30.40 37.93 -25.83 -7.87
CHI 5.73 30.56 -4.15 1.70 -10.18 -12.01 28.65 -10.46 -0.50 -6.88 -2.88
DAL -2.15 1.93 15.92 1.40 6.31 -20.09 27.57 -10.06 -0.35 -24.82 18.24
DEN 13.12 19.34 -4.17 -4.35 -2.02 -5.67 18.03 -0.46 -8.84 -16.84 -4.91
DET -18.78 36.05 -16.00 34.52 -25.66 -0.60 45.51 -8.23 -17.04 -4.32 -11.86
ELP -5.56 11.03 -7.28 30.71 -55.19 13.41 60.22 -31.54 88.24 -32.29 -16.15
HOU 16.45 10.01 -2.81 5.42 -8.46 -9.97 14.10 -4.24 3.04 -17.08 10.68
LOS 6.48 10.53 -3.72 2.32 -11.78 9.59 9.53 -13.98 15.75 -7.31 -4.48
MIA 8.02 4.24 -2.03 1.38 -5.46 -1.44 6.96 21.23 -5.65 -11.38 -6.76
NEW 14.79 19.63 5.64 1.94 2.38 -7.44 -10.05 -3.91 36.05 -26.50 -6.98
NOL 7.89 13.58 4.14 -3.31 -4.11 -2.86 34.80 -8.55 -10.14 -16.37 5.56
NYC 18.66 3.77 -10.91 6.12 -16.03 -2.29 46.88 -26.06 38.13 -20.31 1.31
PHI -1.81 4.43 -9.19 35.02 -9.80 -10.86 27.24 -30.14 66.94 -24.88 2.25
PHO 7.03 0.21 -4.44 -8.19 0.00 -5.54 -0.26 6.91 45.69 -33.33 19.70
SEA 36.42 1.69 5.42 -11.07 0.00 -3.11 13.30 -15.79 12.98 -11.91 11.59
SFR 20.00 5.03 -0.99 -0.33 -13.57 2.52 18.90 -14.79 11.94 -17.67 -0.81
SLC -4.88 5.13 2.68 11.64 -25.11 -24.43 33.08 -3.39 48.83 -26.72 -8.58
SNA 10.99 24.19 -7.45 -8.19 12.15 -14.13 24.44 -15.53 31.00 -17.29 7.15
SND 50.59 -23.36 8.56 17.35 -27.96 8.96 12.67 -4.26 -9.21 -8.04 19.77
SPM 11.39 31.25 -5.19 -3.65 5.69 24.22 0.00 -11.55 -33.47 -7.98 13.33
WAS 8.56 16.26 -17.80 32.99 -7.75 12.18 1.12 1.11 0.37 -12.04 0.41 
arrests_2016 %>% select(1, 14:24) %>%
  pivot_longer(cols = 2:12, names_to = "Type", values_to = "Value") %>%
  ggplot(aes(x = aor, y = Value)) + geom_bar(stat = "identity") + facet_wrap(~Type) + 
  scale_y_continuous(labels = scales::percent_format(scale = 1)) +
  coord_flip() +
  labs(title = "Monthly % change in arrests by AOR in FY 2016") +
  theme_few()

arrests_2017 <- arrests %>% filter(fy == 2017) %>%
  group_by(aor, apprehension_month) %>%
  summarize(total = n()) %>%
  pivot_wider(names_from = apprehension_month, values_from = total) 

colnames(arrests_2017) <- c("aor", "jan", "feb", "mar", "apr", "may", "jun", "jul", "aug", "sep", "oct", "nov", "dec")

arrests_2017 <- arrests_2017 %>% rowwise() %>% 
  mutate(pct_change_02 = round(((feb-jan)/jan) * 100, 2),
         pct_change_03 = round(((mar-feb)/feb) * 100, 2),
         pct_change_04 = round(((apr-mar)/mar) * 100, 2),
         pct_change_05 = round(((may-apr)/apr) * 100, 2),
         pct_change_06 = round(((jun-may)/may) * 100, 2),
         pct_change_07 = round(((jul-jun)/jun) * 100, 2),
         pct_change_08 = round(((aug-jul)/jul) * 100, 2),
         pct_change_09 = round(((sep-aug)/aug) * 100, 2),
         pct_change_10 = round(((oct-sep)/sep) * 100, 2),
         pct_change_11 = round(((nov-oct)/oct) * 100, 2),
         pct_change_12 = round(((dec-nov)/nov) * 100, 2))

arrests_2017 %>% select(1, 14:24) %>% kable(caption = "Monthly % change in arrests by AOR in FY 2017")
Monthly % change in arrests by AOR in FY 2017
aor pct_change_02 pct_change_03 pct_change_04 pct_change_05 pct_change_06 pct_change_07 pct_change_08 pct_change_09 pct_change_10 pct_change_11 pct_change_12
ATL 67.99 21.42 -19.25 18.36 4.84 -8.23 7.80 -11.30 -54.77 0.51 5.54
BAL 4.29 5.48 -29.22 55.96 -7.06 -7.59 8.22 -2.53 -23.38 -11.02 2.86
BOS 18.63 19.01 -14.24 10.93 2.55 -6.41 -0.38 9.54 -47.04 17.11 -12.36
BUF 27.17 13.68 -27.07 53.61 -7.38 11.59 1.30 -5.77 -10.20 -24.24 -21.00
CHI 27.49 3.66 -11.07 5.20 0.39 6.87 6.42 -11.62 -19.33 -19.81 -2.79
DAL 16.23 10.01 -2.88 -10.50 13.05 -0.39 8.25 -6.47 -38.62 17.39 1.35
DEN -1.40 11.37 11.49 -6.87 -9.84 5.91 33.05 -8.06 -40.00 9.94 -7.98
DET 10.04 19.05 -12.33 29.66 30.21 -23.42 1.76 -4.34 -48.94 12.43 7.37
ELP 64.29 -13.04 23.12 12.18 -29.41 -14.74 14.29 13.82 -10.98 -20.13 3.25
HOU 3.00 32.46 -10.12 4.00 5.26 -7.61 -12.11 -3.03 -2.18 -4.07 -6.56
LOS 27.95 4.84 -4.90 29.12 -14.46 1.73 9.55 -1.67 -25.27 -12.52 12.08
MIA 42.22 31.91 -18.85 2.60 24.83 -6.22 -10.25 -48.71 -3.13 16.50 -2.22
NEW 24.89 13.77 -6.69 -1.02 17.93 -21.35 12.27 1.32 -40.52 3.85 8.47
NOL 15.34 31.74 -15.21 15.87 2.47 8.16 27.56 -18.60 -44.05 0.43 -6.57
NYC 53.08 8.04 -2.33 12.38 16.10 17.15 -12.46 4.98 -52.20 13.48 -28.75
PHI 69.50 26.65 -15.83 17.31 -18.76 -7.40 11.14 8.93 -43.60 -7.45 17.24
PHO 2.82 12.94 -11.98 9.86 11.31 3.39 -1.72 -16.67 -1.71 -18.22 8.29
SEA 7.45 48.91 -35.29 18.18 -9.29 8.48 -7.17 5.96 -24.17 4.80 -9.58
SFR 3.96 50.64 -16.03 19.43 5.61 -8.63 7.27 -17.48 -18.06 -1.20 2.23
SLC -18.81 56.78 -8.29 -18.07 16.07 -0.21 -2.07 -5.92 -22.02 -1.15 -3.50
SNA 9.31 35.89 -13.51 1.42 7.56 -3.12 11.83 -13.70 22.42 -32.42 0.34
SND 44.49 9.81 5.71 13.62 -5.79 -12.94 27.71 -12.82 -41.86 11.28 -4.90
SPM 13.39 54.51 -21.35 24.29 -8.28 -6.52 21.72 -10.35 -31.94 -10.83 -0.40
WAS 22.41 11.55 -26.52 30.93 -8.14 14.57 1.25 4.93 -37.32 11.99 0.67 
arrests_2017 %>% select(1, 14:24) %>%
  pivot_longer(cols = 2:12, names_to = "Type", values_to = "Value") %>%
  ggplot(aes(x = aor, y = Value)) + geom_bar(stat = "identity") + facet_wrap(~Type) + 
  scale_y_continuous(labels = scales::percent_format(scale = 1)) +
  coord_flip() +
  labs(title = "Monthly % change in arrests by AOR in FY 2017") +
  theme_few()

arrests_2018 <- arrests %>% filter(fy == 2018) %>%
  group_by(aor, apprehension_month) %>%
  summarize(total = n()) %>%
  pivot_wider(names_from = apprehension_month, values_from = total) 

colnames(arrests_2018) <- c("aor", "jan", "feb", "mar", "apr", "may", "jun", "jul", "aug", "sep", "oct", "nov", "dec")

arrests_2018 <- arrests_2018 %>% rowwise() %>% 
  mutate(pct_change_02 = round(((feb-jan)/jan) * 100, 2),
         pct_change_03 = round(((mar-feb)/feb) * 100, 2),
         pct_change_04 = round(((apr-mar)/mar) * 100, 2),
         pct_change_05 = round(((may-apr)/apr) * 100, 2),
         pct_change_06 = round(((jun-may)/may) * 100, 2),
         pct_change_07 = round(((jul-jun)/jun) * 100, 2),
         pct_change_08 = round(((aug-jul)/jul) * 100, 2),
         pct_change_09 = round(((sep-aug)/aug) * 100, 2),
         pct_change_10 = round(((oct-sep)/sep) * 100, 2),
         pct_change_11 = round(((nov-oct)/oct) * 100, 2),
         pct_change_12 = round(((dec-nov)/nov) * 100, 2))

arrests_2018 %>% select(1, 14:24) %>% kable(caption = "Monthly % change in arrests by AOR in FY 2018")
Monthly % change in arrests by AOR in FY 2018
aor pct_change_02 pct_change_03 pct_change_04 pct_change_05 pct_change_06 pct_change_07 pct_change_08 pct_change_09 pct_change_10 pct_change_11 pct_change_12
ATL 11.35 -9.40 5.55 -2.06 -9.95 2.25 15.71 -20.22 29.19 -5.31 -11.29
BAL -6.90 10.37 -6.04 29.29 -29.28 18.75 -11.18 -9.63 22.13 -8.72 -3.68
BOS -13.91 21.40 -14.39 5.88 -1.59 6.05 -6.08 -16.19 10.63 10.48 -21.74
BUF -33.71 27.59 -19.59 -1.68 6.84 -1.60 45.53 -35.20 16.38 -11.11 -9.17
CHI -9.81 13.50 -10.92 15.67 -18.14 9.64 10.24 -16.55 18.69 -12.14 1.50
DAL -3.36 -0.66 -6.14 6.33 5.23 -10.00 9.43 -12.96 6.24 -6.35 -0.96
DEN -12.45 5.17 -24.18 31.89 -17.21 2.97 17.79 -18.78 33.17 -1.13 -22.14
DET -10.84 -2.03 -11.03 35.27 -27.22 33.46 -1.77 -1.50 -18.90 7.89 -5.23
ELP 21.43 -8.02 -4.07 65.45 -30.77 -12.17 19.28 -13.13 -5.81 -0.62 -0.62
HOU 3.22 3.19 3.56 15.62 -16.42 -28.31 16.29 -13.36 35.87 -14.97 2.97
LOS 21.53 -7.17 5.05 -11.41 12.37 -14.78 13.27 -6.88 39.26 -13.61 -1.26
MIA 9.63 36.21 -27.47 20.71 7.50 -8.27 15.34 -16.54 -30.92 5.23 1.47
NEW 2.56 -8.21 15.18 -7.77 12.82 -11.36 -4.76 -1.15 40.08 -26.67 16.67
NOL -3.68 5.43 -0.12 -2.11 1.20 -3.07 23.90 -17.42 11.80 -15.88 4.44
NYC 9.35 1.12 68.75 -40.31 10.22 5.96 -15.00 -8.46 19.28 7.74 -38.75
PHI 2.25 -7.25 4.03 3.64 -10.33 -6.86 16.84 -20.72 42.05 -10.60 -11.41
PHO -6.09 9.65 -0.30 -1.52 -8.96 -13.58 25.93 -22.31 27.71 -23.11 21.88
SEA -8.83 -11.42 1.95 14.18 -37.92 22.70 7.49 0.82 -1.63 8.26 -8.40
SFR 31.41 -22.39 -0.58 7.45 -17.88 -0.44 8.48 -21.60 79.00 -21.70 -7.49
SLC -18.67 10.73 -0.97 -8.61 1.71 5.26 -7.40 8.64 -8.75 -9.80 -1.69
SNA 3.36 9.85 -15.19 33.05 -3.61 -21.09 6.77 -8.68 -3.65 4.68 -0.48
SND -14.33 65.89 -10.28 1.80 6.84 -14.46 -6.28 -17.01 124.53 46.06 -45.93
SPM -0.27 8.87 -14.81 33.62 -11.50 1.96 -5.77 -15.82 24.55 -3.16 -12.81
WAS 1.75 -4.02 8.08 8.31 -9.97 34.66 -20.04 -23.48 43.79 -16.55 -7.18 
arrests_2018 %>% select(1, 14:24) %>%
  pivot_longer(cols = 2:12, names_to = "Type", values_to = "Value") %>%
  ggplot(aes(x = aor, y = Value)) + geom_bar(stat = "identity") + facet_wrap(~Type) + 
  scale_y_continuous(labels = scales::percent_format(scale = 1)) +
  coord_flip() +
  labs(title = "Monthly % change in arrests by AOR in FY 2018") +
  theme_few()

arrests_2019 <- arrests %>% filter(fy == 2019) %>%
  group_by(aor, apprehension_month) %>%
  summarize(total = n()) %>%
  pivot_wider(names_from = apprehension_month, values_from = total) 

colnames(arrests_2019) <- c("aor", "jan", "feb", "mar", "apr", "may", "jun", "jul", "aug", "sep", "oct", "nov", "dec")

arrests_2019 <- arrests_2019 %>% rowwise() %>% 
  mutate(pct_change_02 = round(((feb-jan)/jan) * 100, 2),
         pct_change_03 = round(((mar-feb)/feb) * 100, 2),
         pct_change_04 = round(((apr-mar)/mar) * 100, 2),
         pct_change_05 = round(((may-apr)/apr) * 100, 2),
         pct_change_06 = round(((jun-may)/may) * 100, 2),
         pct_change_07 = round(((jul-jun)/jun) * 100, 2),
         pct_change_08 = round(((aug-jul)/jul) * 100, 2),
         pct_change_09 = round(((sep-aug)/aug) * 100, 2),
         pct_change_10 = round(((oct-sep)/sep) * 100, 2),
         pct_change_11 = round(((nov-oct)/oct) * 100, 2),
         pct_change_12 = round(((dec-nov)/nov) * 100, 2))

arrests_2019 %>% select(1, 14:24) %>% kable(caption = "Monthly % change in arrests by AOR in FY 2019")
Monthly % change in arrests by AOR in FY 2019
aor pct_change_02 pct_change_03 pct_change_04 pct_change_05 pct_change_06 pct_change_07 pct_change_08 pct_change_09 pct_change_10 pct_change_11 pct_change_12
ATL 7.89 -12.13 -12.35 16.05 -9.70 -10.64 4.49 15.90 -0.86 -12.88 NA
BAL -5.93 11.71 5.65 5.34 -6.52 -13.18 0.89 8.85 -6.50 0.87 NA
BOS -8.13 3.10 -14.59 27.14 -50.20 22.22 -1.30 56.58 -3.78 -8.30 NA
BUF -18.75 8.65 -23.01 55.17 -19.26 -19.27 20.45 47.17 -44.87 9.30 NA
CHI -11.25 -0.29 4.19 4.85 -4.23 -15.06 0.33 31.77 -12.05 -10.49 NA
DAL 3.90 -7.10 15.66 -9.54 -7.10 11.27 -2.45 -3.07 -0.86 -2.54 NA
DEN -10.36 -7.11 3.35 -11.11 -3.12 5.91 -14.72 16.07 0.00 -7.18 NA
DET 25.00 -23.66 15.87 -8.28 -11.11 7.03 -13.87 20.34 -6.69 0.75 NA
ELP 41.92 16.73 -60.37 91.54 -46.99 -2.27 19.38 27.92 -16.24 -15.15 NA
HOU -14.83 14.29 4.94 -5.11 -13.29 21.29 -3.31 8.51 3.15 5.56 -99.65
LOS 7.18 -8.51 -0.40 13.32 -5.26 53.15 -30.96 -8.23 -1.91 1.75 NA
MIA 2.08 -1.92 -0.37 0.25 -11.03 4.13 11.77 -14.91 9.60 -2.92 NA
NEW 3.14 9.57 -0.79 -2.40 -8.20 -1.34 4.98 6.03 7.32 1.52 NA
NOL -9.03 -0.74 -8.50 15.98 -10.72 26.91 -3.12 -4.40 -13.36 -11.01 NA
NYC -36.45 27.41 -20.32 5.50 -18.48 -30.23 15.83 135.97 -44.51 -6.04 NA
PHI -19.15 11.84 -5.29 3.42 -0.90 18.48 -20.97 15.21 -0.84 -4.25 NA
PHO -5.16 -2.72 -3.99 10.40 -9.23 -6.85 0.45 10.64 -0.60 -4.84 NA
SEA -6.22 6.64 -24.90 16.57 -11.37 8.02 3.96 -1.43 -7.25 19.79 NA
SFR -22.43 8.19 -11.14 7.27 -8.88 5.38 -18.25 55.06 -22.84 8.96 NA
SLC -11.93 12.19 -2.62 -5.17 -14.60 15.31 -12.17 17.13 11.40 -22.01 NA
SNA 24.77 -20.04 44.48 48.21 -40.53 -26.81 4.07 0.83 -17.04 -1.13 NA
SND -30.83 -17.71 18.06 1.76 0.00 -4.05 -18.67 92.59 -2.69 -18.97 NA
SPM 1.08 -2.41 9.34 -11.81 -5.41 5.72 9.69 -4.42 -1.09 -3.85 NA
WAS 3.85 4.56 -13.62 -2.52 -24.60 42.49 -6.33 31.83 -10.00 7.59 NA 
arrests_2019 %>% select(1, 14:24) %>%
  pivot_longer(cols = 2:12, names_to = "Type", values_to = "Value") %>%
  ggplot(aes(x = aor, y = Value)) + geom_bar(stat = "identity") + facet_wrap(~Type) + 
  scale_y_continuous(labels = scales::percent_format(scale = 1)) +
  coord_flip() +
  labs(title = "Monthly % change in arrests by AOR in FY 2019") +
  theme_few()

# Quarterly data
arrests_2016_q <- arrests %>% filter(fy == 2016) %>% 
  mutate(quarter = case_when(apprehension_month_y < "2016-01" ~ 1,
                             apprehension_month_y < "2016-04" ~ 2,
                            apprehension_month_y < "2016-07" ~ 3,
                             TRUE ~ 4)) %>%
  group_by(aor, quarter) %>%
  summarize(total = n()) %>%
  pivot_wider(names_from = quarter, values_from = total) 

colnames(arrests_2016_q) <- c("aor", "q1", "q2", "q3", "q4")

arrests_2016_q <- arrests_2016_q %>% rowwise() %>% 
  mutate(pct_change_q2 = round(((q2-q1)/q1) * 100, 2),
         pct_change_q3 = round(((q3-q2)/q2) * 100, 2),
         pct_change_q4 = round(((q4-q3)/q3) * 100, 2))

arrests_2016_q %>% kable(caption = "Quarterly % change in arrests by AOR in FY 2016")
Quarterly % change in arrests by AOR in FY 2016
aor q1 q2 q3 q4 pct_change_q2 pct_change_q3 pct_change_q4
ATL 2420 2208 2036 2202 -8.76 -7.79 8.15
BAL 275 310 317 337 12.73 2.26 6.31
BOS 410 413 538 497 0.73 30.27 -7.62
BUF 291 283 299 300 -2.75 5.65 0.33
CHI 1690 1681 1896 1788 -0.53 12.79 -5.70
DAL 2234 2211 2656 2533 -1.03 20.13 -4.63
DEN 514 557 599 614 8.37 7.54 2.50
DET 518 528 562 633 1.93 6.44 12.63
ELP 431 431 405 344 0.00 -6.03 -15.06
HOU 3071 3163 3451 3211 3.00 9.11 -6.95
LOS 1963 1851 1893 1944 -5.71 2.27 2.69
MIA 906 840 859 919 -7.28 2.26 6.98
NEW 566 500 631 550 -11.66 26.20 -12.84
NOL 1229 1173 1311 1461 -4.56 11.76 11.44
NYC 500 458 434 455 -8.40 -5.24 4.84
PHI 1043 830 917 882 -20.42 10.48 -3.82
PHO 1501 1386 1282 1201 -7.66 -7.50 -6.32
SEA 673 649 703 673 -3.57 8.32 -4.27
SFR 1584 1661 1712 1694 4.86 3.07 -1.05
SLC 1223 1210 1243 962 -1.06 2.73 -22.61
SNA 2339 1936 2087 2063 -17.23 7.80 -1.15
SND 864 926 957 936 7.18 3.35 -2.19
SPM 483 565 653 799 16.98 15.58 22.36
WAS 757 626 690 810 -17.31 10.22 17.39 
arrests_2016_q %>% select(1, 6:8) %>%
  pivot_longer(cols = 2:4, names_to = "Type", values_to = "Value") %>%
  ggplot(aes(x = aor, y = Value)) + geom_bar(stat = "identity") + facet_wrap(~Type) + 
  scale_y_continuous(labels = scales::percent_format(scale = 1)) +
  coord_flip() +
  labs(title = "Quarterly % change in arrests by AOR in FY 2016") +
  theme_few()

arrests_2017_q <- arrests %>% filter(fy == 2017) %>% 
  mutate(quarter = case_when(apprehension_month_y < "2017-01" ~ 1,
                             apprehension_month_y < "2017-04" ~ 2,
                            apprehension_month_y < "2017-07" ~ 3,
                             TRUE ~ 4)) %>%
  group_by(aor, quarter) %>%
  summarize(total = n()) %>%
  pivot_wider(names_from = quarter, values_from = total) 

colnames(arrests_2017_q) <- c("aor", "q1", "q2", "q3", "q4")

arrests_2017_q <- arrests_2016_q %>% rowwise() %>% 
  mutate(pct_change_q2 = round(((q2-q1)/q1) * 100, 2),
         pct_change_q3 = round(((q3-q2)/q2) * 100, 2),
         pct_change_q4 = round(((q4-q3)/q3) * 100, 2))

arrests_2017_q %>% kable(caption = "Quarterly % change in arrests by AOR in FY 2017")
Quarterly % change in arrests by AOR in FY 2017
aor q1 q2 q3 q4 pct_change_q2 pct_change_q3 pct_change_q4
ATL 2420 2208 2036 2202 -8.76 -7.79 8.15
BAL 275 310 317 337 12.73 2.26 6.31
BOS 410 413 538 497 0.73 30.27 -7.62
BUF 291 283 299 300 -2.75 5.65 0.33
CHI 1690 1681 1896 1788 -0.53 12.79 -5.70
DAL 2234 2211 2656 2533 -1.03 20.13 -4.63
DEN 514 557 599 614 8.37 7.54 2.50
DET 518 528 562 633 1.93 6.44 12.63
ELP 431 431 405 344 0.00 -6.03 -15.06
HOU 3071 3163 3451 3211 3.00 9.11 -6.95
LOS 1963 1851 1893 1944 -5.71 2.27 2.69
MIA 906 840 859 919 -7.28 2.26 6.98
NEW 566 500 631 550 -11.66 26.20 -12.84
NOL 1229 1173 1311 1461 -4.56 11.76 11.44
NYC 500 458 434 455 -8.40 -5.24 4.84
PHI 1043 830 917 882 -20.42 10.48 -3.82
PHO 1501 1386 1282 1201 -7.66 -7.50 -6.32
SEA 673 649 703 673 -3.57 8.32 -4.27
SFR 1584 1661 1712 1694 4.86 3.07 -1.05
SLC 1223 1210 1243 962 -1.06 2.73 -22.61
SNA 2339 1936 2087 2063 -17.23 7.80 -1.15
SND 864 926 957 936 7.18 3.35 -2.19
SPM 483 565 653 799 16.98 15.58 22.36
WAS 757 626 690 810 -17.31 10.22 17.39 
arrests_2017_q %>% select(1, 6:8) %>%
  pivot_longer(cols = 2:4, names_to = "Type", values_to = "Value") %>%
  ggplot(aes(x = aor, y = Value)) + geom_bar(stat = "identity") + facet_wrap(~Type) + 
  scale_y_continuous(labels = scales::percent_format(scale = 1)) +
  coord_flip() +
  labs(title = "Quarterly % change in arrests by AOR in FY 2017") +
  theme_few()

arrests_2018_q <- arrests %>% filter(fy == 2018) %>% 
  mutate(quarter = case_when(apprehension_month_y < "2018-01" ~ 1,
                             apprehension_month_y < "2018-04" ~ 2,
                            apprehension_month_y < "2018-07" ~ 3,
                             TRUE ~ 4)) %>%
  group_by(aor, quarter) %>%
  summarize(total = n()) %>%
  pivot_wider(names_from = quarter, values_from = total) 

colnames(arrests_2018_q) <- c("aor", "q1", "q2", "q3", "q4")

arrests_2018_q <- arrests_2018_q %>% rowwise() %>% 
  mutate(pct_change_q2 = round(((q2-q1)/q1) * 100, 2),
         pct_change_q3 = round(((q3-q2)/q2) * 100, 2),
         pct_change_q4 = round(((q4-q3)/q3) * 100, 2))

arrests_2018_q %>% kable(caption = "Quarterly % change in arrests by AOR in FY 2018")
Quarterly % change in arrests by AOR in FY 2018
aor q1 q2 q3 q4 pct_change_q2 pct_change_q3 pct_change_q4
ATL 3935 3850 3757 3647 -2.16 -2.42 -2.93
BAL 416 429 449 409 3.12 4.66 -8.91
BOS 680 773 738 717 13.68 -4.53 -2.85
BUF 364 439 361 418 20.60 -17.77 15.79
CHI 2305 2355 2278 2303 2.17 -3.27 1.10
DAL 4147 4615 4522 4360 11.29 -2.02 -3.58
DEN 731 741 631 652 1.37 -14.84 3.33
DET 825 918 861 1000 11.27 -6.21 16.14
ELP 483 513 627 536 6.21 22.22 -14.51
HOU 3459 3757 4178 2939 8.62 11.21 -29.66
LOS 2255 1879 1919 1801 -16.67 2.13 -6.15
MIA 1610 2161 2285 2418 34.22 5.74 5.82
NEW 932 810 877 790 -13.09 8.27 -9.92
NOL 2551 2508 2536 2675 -1.69 1.12 5.48
NYC 813 787 1035 841 -3.20 31.51 -18.74
PHI 1343 1322 1302 1176 -1.56 -1.51 -9.68
PHO 1721 1900 1893 1648 10.40 -0.37 -12.94
SEA 744 862 744 717 15.86 -13.69 -3.63
SFR 1710 1677 1508 1315 -1.93 -10.08 -12.80
SLC 1280 1555 1453 1466 21.48 -6.56 0.89
SNA 2443 2738 3006 2562 12.08 9.79 -14.77
SND 2350 1144 1382 1124 -51.32 20.80 -18.67
SPM 1156 1150 1214 1138 -0.52 5.57 -6.26
WAS 1088 1024 1104 1143 -5.88 7.81 3.53 
arrests_2018_q %>% select(1, 6:8) %>%
  pivot_longer(cols = 2:4, names_to = "Type", values_to = "Value") %>%
  ggplot(aes(x = aor, y = Value)) + geom_bar(stat = "identity") + facet_wrap(~Type) + 
  scale_y_continuous(labels = scales::percent_format(scale = 1)) +
  coord_flip() +
  labs(title = "Quarterly % change in arrests by AOR in FY 2018") +
  theme_few()

arrests_2019_q <- arrests %>% filter(fy == 2019) %>% 
  mutate(quarter = case_when(apprehension_month_y < "2019-01" ~ 1,
                             apprehension_month_y < "2019-04" ~ 2,
                            apprehension_month_y < "2019-07" ~ 3,
                             TRUE ~ 4)) %>%
  group_by(aor, quarter) %>%
  summarize(total = n()) %>%
  pivot_wider(names_from = quarter, values_from = total) 

colnames(arrests_2019_q) <- c("aor", "q1", "q2", "q3", "q4")

arrests_2019_q <- arrests_2019_q %>% rowwise() %>% 
  mutate(pct_change_q2 = round(((q2-q1)/q1) * 100, 2),
         pct_change_q3 = round(((q3-q2)/q2) * 100, 2),
         pct_change_q4 = round(((q4-q3)/q3) * 100, 2))

arrests_2019_q %>% kable(caption = "Quarterly % change in arrests by AOR in FY 2019")
Quarterly % change in arrests by AOR in FY 2019
aor q1 q2 q3 q4 pct_change_q2 pct_change_q3 pct_change_q4
ATL 3309 3723 3279 1957 12.51 -11.93 -40.32
BAL 354 353 398 225 -0.28 12.75 -43.47
BOS 677 705 578 306 4.14 -18.01 -47.06
BUF 336 345 331 194 2.68 -4.06 -41.39
CHI 2168 2168 2201 1232 0.00 1.52 -44.03
DAL 4114 4233 4318 2906 2.89 2.01 -32.70
DEN 571 685 594 365 19.96 -13.28 -38.55
DET 816 910 858 510 11.52 -5.71 -40.56
ELP 502 807 511 283 60.76 -36.68 -44.62
HOU 3264 2763 2769 1965 -15.35 0.22 -29.04
LOS 1561 1572 1613 1398 0.70 2.61 -13.33
MIA 2272 2466 2356 1601 8.54 -4.46 -32.05
NEW 778 705 718 453 -9.38 1.84 -36.91
NOL 2350 2492 2339 1894 6.04 -6.14 -19.03
NYC 681 758 583 259 11.31 -23.09 -55.57
PHI 1047 1020 985 700 -2.58 -3.43 -28.93
PHO 1467 1559 1494 900 6.27 -4.17 -39.76
SEA 629 708 579 412 12.56 -18.22 -28.84
SFR 1361 1399 1217 747 2.79 -13.01 -38.62
SLC 1387 1443 1335 849 4.04 -7.48 -36.40
SNA 2255 2830 4228 1655 25.50 49.40 -60.86
SND 718 572 516 301 -20.33 -9.79 -41.67
SPM 1082 1106 1081 736 2.22 -2.26 -31.91
WAS 1176 1056 859 643 -10.20 -18.66 -25.15 
arrests_2019_q %>% select(1, 6:8) %>%
  pivot_longer(cols = 2:4, names_to = "Type", values_to = "Value") %>%
  ggplot(aes(x = aor, y = Value)) + geom_bar(stat = "identity") + facet_wrap(~Type) + 
  scale_y_continuous(labels = scales::percent_format(scale = 1)) +
  coord_flip() +
  labs(title = "Quarterly % change in arrests by AOR in FY 2019") +
  theme_few()

#apprehension_landmark <- arrests %>% group_by(apprehension_landmark) %>% summarize(total = n())
#write.csv(apprehension_landmark, 'apprehension_landmark.csv')

arrests <- arrests %>% mutate(apprehension_landmark2 = 
                                case_when(grepl('NON-SPECIFIC|ARREST AT|ATD|BUREAU OF PRISONS-|FEDCAP-', apprehension_landmark) ~ "Non-specific",
                                          apprehension_landmark %in% c("LICENSING UNIT/STATE POLICE", "287g", "at-large", "California Healthcare Facility", "CALIFORNIA HIGHWAY PATROL", "CAP ACI", "CIS REFERRAL", "FEDERAL DETENTION CENTER (FDC)", "FIELD ARREST", "FTC CI (Federal Transfer Center)", "FTM-JCART", "FTM-VCAS", "FUG - NON FUGITIVE", "FUGITIVE OPERATIONS", "FUGITIVE SOUTH TEAM ARRESTS", "FUGOP", "FUGOPS", "STREET ARREST", "STREET ARRESTS", "U.S. Marshalls Service", "U.S. Marshals", "U.S. Marshals Service", "U.S. MARSHALS SERVICE"< "U.S. PROBATION OFFICE", "UNITED STATES MARSHALL SERVICE", "UNITED STATES PROBATION & PAROLE", "UNITED STATES PROBATION", "US 281 TO FM 493 EXP 83 NORTH TO FM 490", "US DISTRICT COURT", "US MARSHALLS", "US Marshals TF", "USCIS ARREST", "USCIS REFERRALS") ~ "Non-specific",
                                          apprehension_landmark == 'DO NOT USE'  ~ "DO NOT USE",
                                          apprehension_landmark == ''  ~ "NA",
                                          TRUE ~ "Specific"))
#test <- arrests %>% select(apprehension_landmark, apprehension_landmark2)

specificity <- arrests %>% 
  group_by(aor, apprehension_landmark2) %>%
  summarize(total = n()) %>% 
  pivot_wider(names_from = apprehension_landmark2, values_from = total)
  
specificity %>% kable(caption = 'Apprehension landmark specificity by AOR FY2016-2019')
Apprehension landmark specificity by AOR FY2016-2019
aor NA Non-specific Specific DO NOT USE
ATL 845 3931 45098 NA
BAL 83 149 5706 NA
BOS 77 1486 8303 NA
BUF 18 380 4929 128
CHI 2103 10270 20296 NA
DAL 133 4699 54537 NA
DEN 72 614 9314 NA
DET 235 3566 8231 316
ELP 1276 2343 4146 NA
HOU 230 10190 39494 1641
LOS 719 17217 12132 NA
MIA 388 7408 19088 1
NEW 43 715 10741 NA
NOL 312 10758 21398 19
NYC 67 1422 8690 1
PHI 211 3995 13299 NA
PHO 1167 17 23224 1
SEA 114 394 10961 NA
SFR 239 8598 15979 NA
SLC 146 4817 15620 NA
SNA 4059 18422 16170 1
SND 1352 3532 11123 334
SPM 52 4748 10538 NA
WAS 93 4658 10388 NA 
specificity2 <- specificity %>% 
  replace(is.na(.), 0) %>%
  rowwise() %>%
  mutate("Non-specific total" = sum(c_across("NA":"DO NOT USE"))) %>%
  select("Non-specific total", "Specific") %>%
  rowwise() %>%
  mutate("Total" =  sum(c_across("Non-specific total": "Specific"))) %>%
  rowwise() %>% mutate("%specific" = round((Specific/Total), 2))

specificity2 %>% kable(caption = 'Apprehension landmark specificity % by AOR FY2016-2019')
Apprehension landmark specificity % by AOR FY2016-2019
aor Non-specific total Specific Total %specific
ATL 49874 45098 94972 0.47
BAL 5938 5706 11644 0.49
BOS 9866 8303 18169 0.46
BUF 5455 4929 10384 0.47
CHI 32669 20296 52965 0.38
DAL 59369 54537 113906 0.48
DEN 10000 9314 19314 0.48
DET 12348 8231 20579 0.40
ELP 7765 4146 11911 0.35
HOU 51555 39494 91049 0.43
LOS 30068 12132 42200 0.29
MIA 26885 19088 45973 0.42
NEW 11499 10741 22240 0.48
NOL 32487 21398 53885 0.40
NYC 10180 8690 18870 0.46
PHI 17505 13299 30804 0.43
PHO 24409 23224 47633 0.49
SEA 11469 10961 22430 0.49
SFR 24816 15979 40795 0.39
SLC 20583 15620 36203 0.43
SNA 38652 16170 54822 0.29
SND 16341 11123 27464 0.41
SPM 15338 10538 25876 0.41
WAS 15139 10388 25527 0.41 
arrests %>% 
  group_by(apprehension_method) %>%
  summarize(total = n()) %>%
  arrange(desc(total)) %>%
  kable(caption = 'Arrests - Apprehension methods')
Arrests - Apprehension methods
apprehension_method total
CAP Local Incarceration 213483
CAP Federal Incarceration 84440
Located 66851
Non-Custodial Arrest 61258
CAP State Incarceration 41119
287(g) Program 28973
ERO Reprocessed Arrest 17786
Probation and Parole 14837
Other efforts 5389
Law Enforcement Agency Response Unit 2487
Other Agency (turned over to INS) 1582
Other Task Force 544
Organized Crime Drug Enforcement Task Force 321
Patrol Border 306
Worksite Enforcement 281
Inspections 204
Anti-Smuggling 111
Patrol Interior 87
Boat Patrol 38
Traffic Check 38
Crewman/Stowaway 35
Transportation Check Aircraft 25
Criminal Alien Program 10
Transportation Check Bus 2
Transportation Check Passenger Train 2
Transportation Check Freight Train 1 
# by FY
arrests %>% 
  group_by(citizenship, fy) %>%
  summarize(total = n()) %>%
  pivot_wider(names_from = fy, values_from = total) %>%
  replace(is.na(.), 0) %>%
  mutate(total = sum(c_across('2016':'2019'))) %>%
  arrange(desc(total)) %>%
  head(n=15) %>%
  kable(caption = 'Top 15 countries whose citizens were arrested FY2016-2019')
Top 15 countries whose citizens were arrested FY2016-2019
citizenship 2016 2017 2018 2019 total
MEXICO 70779 87367 93298 73879 325323
GUATEMALA 9091 14217 17553 16083 56944
HONDURAS 7633 11828 13546 12343 45350
EL SALVADOR 6753 9240 10083 8356 34432
DOMINICAN REPUBLIC 1842 2019 1875 1698 7434
CUBA 1012 1693 2094 2122 6921
COLOMBIA 873 1013 1097 955 3938
JAMAICA 926 961 1085 840 3812
ECUADOR 661 1014 1028 1026 3729
BRAZIL 437 899 1107 896 3339
HAITI 847 1014 621 498 2980
CHINA, PEOPLES REPUBLIC OF 510 706 999 558 2773
NICARAGUA 424 596 637 765 2422
INDIA 333 496 635 535 1999
VIETNAM 425 456 575 388 1844 
# by AOR

# by FY-AOR
# by FY
arrests %>% 
  group_by(gender, fy) %>%
  summarize(total = n()) %>%
  pivot_wider(names_from = fy, values_from = total) %>%
  mutate(total = sum(c_across('2016':'2019'))) %>%
  arrange(desc(total)) %>%
  kable(caption = 'Gender makeup among those arrested FY2016-2019')
Gender makeup among those arrested FY2016-2019
gender 2016 2017 2018 2019 total
Male 102345 132690 143832 117622 496489
Female 7469 10507 13862 11848 43686
Unknown 6 6 9 14 35 
# by AOR

# by FY-AOR

Encounters

encounters <- read.csv("../../export/output/encounters.csv.gz")
encounters <- encounters %>% filter(aor != "HQ") %>%
  mutate(event_date2 = as.Date(event_date, format = "%m/%d/%Y"),
                              event_year = format(event_date2, "%Y"))
encounters %>% select(1:7) %>% head() %>% kable(caption = "`encounters` dataframe")
encounters dataframe
aor event_date landmark operation processing_disposition citizenship gender
DEN 10/1/2015 EAGLE COUNTY JAIL Not Amenable to Removal MEXICO Male
SFR 10/1/2015 TAFT FEDERAL CORRECTIONAL INSTITUTION (B)(7)(E) ADMINISTRATIVE DEPORTATION I-851/I-851A MEXICO Male
LOS 10/1/2015 WSM GENERAL AREA, NON-SPECIFIC (B)(7)(E) Detainer BELIZE Male
ATL 10/1/2015 Not Amenable to Removal JAMAICA Male
MIA 10/1/2015 Not in Custody ARGENTINA Male
SFR 10/1/2015 ALAMEDA COUNTY JAIL - SANTA RITA Other GUATEMALA Male 
encounters <- encounters %>% mutate(fy = case_when(event_date2 < "2016-10-01" ~ 2016,
                                                   event_date2 < "2017-10-01" ~ 2017,
                                                   event_date2 < "2018-10-01" ~ 2018,
                                                   event_date2 < "2019-10-01" ~ 2019),
                              event_month = format(event_date2, "%m"),
                              event_month2 = format(event_date2, "%Y-%m"),
                              month = month(event_date2))
encounters %>% 
  group_by(fy) %>%
  summarize(total = n()) %>%
  ggplot(aes(fy, total, group = 1)) + 
  geom_point() +
  geom_line() +
  labs(x = "Encounter Year (Fiscal)",
       y = "Total Yearly Encounters",
       title = "Total Encounters Across AOR FY 2016-2019") +
  theme_few()

encounters %>% 
  group_by(month, fy) %>%
  summarize(total = n()) %>%
  mutate(Year = factor(fy)) %>%
  mutate(Month = factor(month,
                        levels = c(10:12, 1:9),
                        labels = c(month.abb[10:12], month.abb[1:9]))) %>%
  ggplot(aes(x = Month, y = total, color = Year, group = Year)) +
  geom_line() +
  geom_point() +
  scale_color_brewer(palette="Dark2") +
  theme_few() +
  theme(legend.position="bottom") +
  labs(color = "FY",
       title = "Total Monthly Encounters Across AOR FY 2016-2019",
       y = "Total Encounters")

encounters %>% 
  group_by(fy, aor) %>%
  summarize(total = n()) %>%
  ggplot(aes(as.numeric(fy), total, group = aor)) + 
  geom_line(aes(col = aor)) +
  scale_x_continuous(limits = c(2016, 2019)) +
  labs(x = "Encounter Year (Fiscal)",
       y = "Yearly Encounters by AOR",
       title = "Total Encounters by AOR FY 2016-2019") +
  facet_wrap(~aor) +
  theme_few() +
  theme(legend.position = "none",
        axis.text.x = element_text(angle = 45))

yearly_change_encounters <- encounters %>% 
  group_by(aor, fy) %>%
  summarize(total = n()) %>%
  pivot_wider(names_from = fy, values_from = total) 

colnames(yearly_change_encounters) <- c("aor", "y_2016", "y_2017", "y_2018", "y_2019")

yearly_change_encounters <- yearly_change_encounters %>% rowwise() %>% 
  mutate(pct_change_17 = round(((y_2017-y_2016)/y_2016) * 100, 2),
         pct_change_18 = round(((y_2018-y_2017)/y_2017) * 100, 2),
         pct_change_19 = round(((y_2019-y_2018)/y_2018) * 100, 2))

yearly_change_encounters %>% kable(caption = "Yearly % change in encounters by AOR")
Yearly % change in encounters by AOR
aor y_2016 y_2017 y_2018 y_2019 pct_change_17 pct_change_18 pct_change_19
ATL 33227 28263 28876 25275 -14.94 2.17 -12.47
BAL 6427 4801 3938 3865 -25.30 -17.98 -1.85
BOS 8869 9491 10339 8482 7.01 8.93 -17.96
BUF 6102 4338 3547 3127 -28.91 -18.23 -11.84
CHI 9446 12814 13251 11147 35.66 3.41 -15.88
DAL 28904 33308 33758 29929 15.24 1.35 -11.34
DEN 10289 9659 8794 7759 -6.12 -8.96 -11.77
DET 8266 9769 9039 5826 18.18 -7.47 -35.55
ELP 3119 3205 3279 3002 2.76 2.31 -8.45
HOU 17112 18404 19295 15311 7.55 4.84 -20.65
LOS 27046 38256 54971 51134 41.45 43.69 -6.98
MIA 27114 31627 45876 45330 16.64 45.05 -1.19
NEW 12279 15105 10975 9728 23.01 -27.34 -11.36
NOL 8728 13382 17099 13588 53.32 27.78 -20.53
NYC 22294 49882 42028 33001 123.75 -15.75 -21.48
PHI 9992 11392 9638 6858 14.01 -15.40 -28.84
PHO 18219 19695 20108 16802 8.10 2.10 -16.44
SEA 11986 13266 14780 12582 10.68 11.41 -14.87
SFR 21523 23292 19060 12936 8.22 -18.17 -32.13
SLC 11637 12247 13126 9889 5.24 7.18 -24.66
SNA 15552 19749 23144 19832 26.99 17.19 -14.31
SND 9237 9166 9961 5905 -0.77 8.67 -40.72
SPM 14819 17698 18080 16730 19.43 2.16 -7.47
WAS 13004 10277 11738 9792 -20.97 14.22 -16.58 
yearly_change_encounters %>% select(aor, 6:8) %>%
  pivot_longer(cols = 2:4, names_to = "Type", values_to = "Value") %>%
  ggplot(aes(x = aor, y = Value)) + geom_bar(stat = "identity") + facet_wrap(~Type) + 
  scale_y_continuous(labels = scales::percent_format(scale = 1)) +
  coord_flip() +
  labs(title = "Yearly % change in Encounters by AOR") +
  theme_few()

# Monthly data
encounters_2016 <- encounters %>% filter(fy == 2016) %>%
  group_by(aor, event_month) %>%
  summarize(total = n()) %>%  
  pivot_wider(names_from = event_month, values_from = total) 

colnames(encounters_2016) <- c("aor", "jan", "feb", "mar", "apr", "may", "jun", "jul", "aug", "sep", "oct", "nov", "dec")

encounters_2016 <- encounters_2016 %>% rowwise() %>% 
  mutate(pct_change_02 = round(((feb-jan)/jan) * 100, 2),
         pct_change_03 = round(((mar-feb)/feb) * 100, 2),
         pct_change_04 = round(((apr-mar)/mar) * 100, 2),
         pct_change_05 = round(((may-apr)/apr) * 100, 2),
         pct_change_06 = round(((jun-may)/may) * 100, 2),
         pct_change_07 = round(((jul-jun)/jun) * 100, 2),
         pct_change_08 = round(((aug-jul)/jul) * 100, 2),
         pct_change_09 = round(((sep-aug)/aug) * 100, 2),
         pct_change_10 = round(((oct-sep)/sep) * 100, 2),
         pct_change_11 = round(((nov-oct)/oct) * 100, 2),
         pct_change_12 = round(((dec-nov)/nov) * 100, 2))

encounters_2016 %>% select(1, 14:24) %>% kable(caption = "Monthly % change in encounters by AOR in FY 2016")
Monthly % change in encounters by AOR in FY 2016
aor pct_change_02 pct_change_03 pct_change_04 pct_change_05 pct_change_06 pct_change_07 pct_change_08 pct_change_09 pct_change_10 pct_change_11 pct_change_12
ATL 15.84 20.05 -8.22 -4.01 -1.20 -21.03 9.22 2.13 68.28 -22.62 -8.04
BAL 19.47 22.22 -20.91 4.41 14.86 -11.18 -23.20 21.78 -0.96 -3.69 14.52
BOS 4.53 72.36 -42.61 -10.14 7.77 -0.99 -1.00 -20.20 34.90 -5.63 -5.68
BUF 9.05 0.00 -8.12 16.47 -15.34 -13.24 16.43 -16.13 63.70 -28.49 -8.42
CHI 3.02 26.65 8.44 -17.06 0.13 12.20 14.76 -7.58 -25.89 -10.77 3.75
DAL -44.44 34.86 27.70 -4.88 2.27 -12.50 8.51 -7.73 -16.21 -28.44 7.78
DEN -10.55 7.62 -4.79 -5.64 2.29 5.22 27.51 -7.69 -14.54 -9.51 -7.13
DET -23.65 53.06 -23.52 -0.32 13.83 -10.47 28.24 18.61 -46.15 -6.67 56.53
ELP 4.80 3.52 4.08 -8.50 -20.00 -19.20 42.54 -29.07 101.09 -27.99 -22.64
HOU 7.08 8.97 -4.15 5.75 -9.10 -3.29 6.81 -1.89 -14.92 -16.13 3.62
LOS -6.06 13.86 6.59 -2.07 -14.46 -0.98 16.78 -6.86 -6.98 -0.61 -10.45
MIA 3.00 71.74 44.97 31.09 -15.66 -21.62 14.80 1.11 -49.10 -29.03 13.79
NEW 11.09 -1.14 -2.42 21.23 -5.33 -6.85 22.28 -0.74 -18.77 -6.65 18.51
NOL 3.07 24.02 -6.84 0.00 2.58 -15.50 42.16 -14.22 -1.29 -14.58 8.08
NYC 30.93 22.95 -49.86 41.41 43.38 62.90 -5.64 -4.77 -34.88 3.42 -3.14
PHI 9.45 27.78 -17.24 8.88 -4.13 -17.69 27.13 -14.52 7.10 -17.87 17.87
PHO -2.36 16.88 -8.31 4.95 -7.48 1.46 3.00 -1.01 -4.42 -17.41 20.92
SEA -0.66 23.27 -13.01 23.36 -13.64 -7.99 18.43 -11.36 6.46 -14.31 -1.77
SFR -7.86 20.21 -6.94 11.46 8.00 -10.18 -2.51 -27.64 38.64 -17.35 21.96
SLC -9.81 6.85 -4.52 6.32 -11.98 2.85 5.95 -5.81 -5.76 -22.36 17.13
SNA 11.72 10.67 5.72 -6.15 -3.71 -2.13 45.68 -0.81 -25.23 -15.67 12.79
SND 6.52 -3.92 -5.73 13.65 -20.93 6.32 7.92 2.62 11.11 -12.30 -3.54
SPM -0.35 20.88 -2.55 -0.60 -8.95 0.83 21.39 -16.95 -10.57 -5.91 18.94
WAS 29.60 14.93 -10.33 9.42 -10.37 -2.85 -2.93 0.19 -8.95 1.14 9.71 
encounters_2016 %>% select(1, 14:24) %>%
  pivot_longer(cols = 2:12, names_to = "Type", values_to = "Value") %>%
  ggplot(aes(x = aor, y = Value)) + geom_bar(stat = "identity") + facet_wrap(~Type) + 
  scale_y_continuous(labels = scales::percent_format(scale = 1)) +
  coord_flip() +
  labs(title = "Monthly % change in encounters by AOR in FY 2016") +
  theme_few()

encounters_2017 <- encounters %>% filter(fy == 2017) %>%
  group_by(aor, event_month) %>%
  summarize(total = n()) %>%  
  pivot_wider(names_from = event_month, values_from = total) 

colnames(encounters_2017) <- c("aor", "jan", "feb", "mar", "apr", "may", "jun", "jul", "aug", "sep", "oct", "nov", "dec")

encounters_2017 <- encounters_2017 %>% rowwise() %>% 
  mutate(pct_change_02 = round(((feb-jan)/jan) * 100, 2),
         pct_change_03 = round(((mar-feb)/feb) * 100, 2),
         pct_change_04 = round(((apr-mar)/mar) * 100, 2),
         pct_change_05 = round(((may-apr)/apr) * 100, 2),
         pct_change_06 = round(((jun-may)/may) * 100, 2),
         pct_change_07 = round(((jul-jun)/jun) * 100, 2),
         pct_change_08 = round(((aug-jul)/jul) * 100, 2),
         pct_change_09 = round(((sep-aug)/aug) * 100, 2),
         pct_change_10 = round(((oct-sep)/sep) * 100, 2),
         pct_change_11 = round(((nov-oct)/oct) * 100, 2),
         pct_change_12 = round(((dec-nov)/nov) * 100, 2))

encounters_2017 %>% select(1, 14:24) %>% kable(caption = "Monthly % change in encounters by AOR in FY 2016")
Monthly % change in encounters by AOR in FY 2016
aor pct_change_02 pct_change_03 pct_change_04 pct_change_05 pct_change_06 pct_change_07 pct_change_08 pct_change_09 pct_change_10 pct_change_11 pct_change_12
ATL 0.13 15.73 -17.15 23.18 -0.95 -3.89 -4.32 -9.79 -4.06 -11.87 4.49
BAL -10.07 18.13 -4.51 3.07 -28.67 37.62 3.27 -12.67 22.02 -30.15 3.34
BOS 36.63 44.85 -22.98 7.18 -22.00 -11.99 72.45 -29.71 -14.05 -5.35 -8.63
BUF -10.64 0.89 -5.31 17.76 8.99 -12.14 -5.25 -8.16 37.14 -24.77 22.77
CHI 1.15 4.95 -11.05 -5.56 0.09 7.54 -2.56 -6.67 -24.34 -10.19 16.18
DAL 3.31 16.94 -19.56 3.72 2.61 4.37 0.42 -3.30 17.41 -20.18 -12.84
DEN -15.38 10.10 -20.99 2.47 -2.83 12.97 14.58 -24.55 59.40 -31.84 16.62
DET 2.88 16.64 -12.47 32.47 0.10 -14.46 16.67 -8.80 -9.76 -8.30 -9.33
ELP 36.82 -5.09 27.20 7.53 -22.41 -3.97 26.32 -28.87 0.00 5.02 -31.87
HOU -0.07 26.28 -13.02 0.18 6.74 -4.30 -19.18 1.85 11.80 -11.99 -5.26
LOS 14.66 10.27 -2.99 14.90 -1.61 33.57 2.68 1.77 -53.28 -14.65 -4.16
MIA -7.26 4.15 -22.39 9.65 10.39 -4.32 2.93 -29.67 35.16 13.80 4.54
NEW -9.70 13.34 -8.79 15.93 -11.93 13.22 -8.12 -4.74 8.37 -6.88 -2.13
NOL 16.60 43.25 -20.18 32.12 -5.46 5.77 9.50 -19.94 -40.82 9.56 -4.11
NYC 58.30 -10.66 -5.38 17.10 -10.73 -7.84 9.46 -11.48 -11.74 -6.12 -5.89
PHI -12.27 26.45 -0.10 13.96 -7.44 5.01 -16.56 -2.91 1.67 -18.14 17.89
PHO -1.60 0.00 -6.83 17.29 1.89 -2.76 1.51 -3.88 -0.18 -5.23 -9.79
SEA -5.72 15.61 -16.77 23.35 -3.18 4.84 12.71 -12.81 -18.64 4.85 -5.71
SFR 8.57 42.81 -21.64 26.71 -8.39 -21.67 1.88 -18.34 8.06 0.99 31.26
SLC -3.61 22.19 -21.23 16.60 -6.94 3.12 -10.99 -11.52 19.77 -0.10 -23.62
SNA -14.04 11.56 16.94 4.40 -0.72 0.00 -2.79 -3.68 20.48 -30.62 5.21
SND 3.87 33.98 -3.23 -2.07 -1.64 -13.13 12.64 -7.32 -7.11 -1.27 -2.58
SPM -12.90 28.06 -11.68 20.63 -12.05 -3.36 0.40 -13.25 11.36 -0.41 -9.27
WAS 19.30 -10.51 -15.96 24.80 -7.67 38.01 2.86 -11.23 4.86 -1.96 -5.89 
encounters_2017 %>% select(1, 14:24) %>%
  pivot_longer(cols = 2:12, names_to = "Type", values_to = "Value") %>%
  ggplot(aes(x = aor, y = Value)) + geom_bar(stat = "identity") + facet_wrap(~Type) + 
  scale_y_continuous(labels = scales::percent_format(scale = 1)) +
  coord_flip() +
  labs(title = "Monthly % change in encounters by AOR in FY 2017") +
  theme_few()

encounters_2018 <- encounters %>% filter(fy == 2018) %>%
  group_by(aor, event_month) %>%
  summarize(total = n()) %>%  
  pivot_wider(names_from = event_month, values_from = total) 

colnames(encounters_2018) <- c("aor", "jan", "feb", "mar", "apr", "may", "jun", "jul", "aug", "sep", "oct", "nov", "dec")

encounters_2018 <- encounters_2018 %>% rowwise() %>% 
  mutate(pct_change_02 = round(((feb-jan)/jan) * 100, 2),
         pct_change_03 = round(((mar-feb)/feb) * 100, 2),
         pct_change_04 = round(((apr-mar)/mar) * 100, 2),
         pct_change_05 = round(((may-apr)/apr) * 100, 2),
         pct_change_06 = round(((jun-may)/may) * 100, 2),
         pct_change_07 = round(((jul-jun)/jun) * 100, 2),
         pct_change_08 = round(((aug-jul)/jul) * 100, 2),
         pct_change_09 = round(((sep-aug)/aug) * 100, 2),
         pct_change_10 = round(((oct-sep)/sep) * 100, 2),
         pct_change_11 = round(((nov-oct)/oct) * 100, 2),
         pct_change_12 = round(((dec-nov)/nov) * 100, 2))

encounters_2018 %>% select(1, 14:24) %>% kable(caption = "Monthly % change in encounters by AOR in FY 2018")
Monthly % change in encounters by AOR in FY 2018
aor pct_change_02 pct_change_03 pct_change_04 pct_change_05 pct_change_06 pct_change_07 pct_change_08 pct_change_09 pct_change_10 pct_change_11 pct_change_12
ATL 10.45 10.25 3.46 8.10 -12.61 4.92 5.93 -10.20 -5.25 -11.22 -4.65
BAL -22.43 40.77 -14.85 -7.56 -6.29 -10.40 23.97 -17.52 56.41 -23.19 -11.28
BOS 9.61 0.92 -4.34 37.75 -12.40 -21.78 21.39 -16.89 9.41 -11.58 -19.97
BUF -20.35 9.26 -11.86 4.23 6.64 -2.77 12.81 -20.50 36.51 -10.47 4.22
CHI -7.71 4.27 -13.79 23.12 -1.76 -4.27 10.70 -11.27 -4.36 -2.85 -2.05
DAL -2.58 4.38 8.47 6.08 -6.56 -0.51 12.60 -12.35 -6.00 -5.53 -2.04
DEN -11.30 -2.03 5.17 8.71 -14.21 1.81 3.11 -11.76 52.03 -10.91 -11.04
DET -17.07 6.29 -5.92 -2.10 -4.69 -12.08 4.31 -4.29 37.12 0.23 -18.16
ELP 15.35 -12.29 -2.33 36.25 -6.43 -4.06 -13.03 1.50 -2.21 -22.26 19.42
HOU -1.85 -1.83 6.22 26.03 -15.29 -14.37 12.02 -19.34 16.96 -7.75 -7.52
LOS -0.23 3.56 8.05 7.25 -12.26 2.35 10.95 -11.90 0.18 -6.46 -6.83
MIA 71.21 -25.46 12.42 10.84 -8.84 5.44 -3.30 -9.72 -32.41 -12.21 -9.51
NEW 18.69 -37.68 35.19 0.80 -22.43 -2.96 -0.66 -1.87 66.98 -14.32 -27.45
NOL 24.44 -3.30 6.61 -3.91 -6.38 42.86 -13.81 -27.21 7.44 -5.50 -11.79
NYC -0.51 8.30 0.44 -8.70 -9.56 5.25 3.70 -7.61 48.70 -9.09 -11.78
PHI 6.68 -21.81 3.31 7.22 -2.74 -13.21 32.05 -29.53 57.78 -15.59 -9.89
PHO -12.23 15.45 -1.14 5.93 -5.66 -3.05 4.34 -10.66 12.05 -13.77 -0.26
SEA -21.02 5.36 1.27 -1.92 -10.58 17.84 5.18 0.15 10.30 -12.13 -11.50
SFR 0.00 1.16 -29.35 17.35 -23.87 3.70 19.76 -28.43 70.00 -4.52 -1.88
SLC -12.03 2.73 28.97 2.06 -8.86 9.30 -5.46 2.48 -28.69 11.53 -19.55
SNA -6.76 21.29 -13.73 6.29 -11.24 19.66 -16.25 1.06 9.18 -1.62 -3.86
SND -12.89 39.09 -3.55 -6.22 48.87 -7.80 -8.02 -21.15 61.82 28.93 -35.58
SPM 0.81 12.02 -3.40 2.47 -4.94 -0.25 9.46 -26.86 12.05 -4.60 -8.31
WAS 0.46 6.47 1.08 8.26 -16.06 40.73 8.72 -23.77 3.34 -7.74 -8.60 
encounters_2018 %>% select(1, 14:24) %>%
  pivot_longer(cols = 2:12, names_to = "Type", values_to = "Value") %>%
  ggplot(aes(x = aor, y = Value)) + geom_bar(stat = "identity") + facet_wrap(~Type) + 
  scale_y_continuous(labels = scales::percent_format(scale = 1)) +
  coord_flip() +
  labs(title = "Monthly % change in encounters by AOR in FY 2018") +
  theme_few()

encounters_2019 <- encounters %>% filter(fy == 2019) %>%
  group_by(aor, event_month) %>%
  summarize(total = n())

# Quarterly data
encounters_2016_q <- encounters %>% filter(fy == 2016) %>% 
  mutate(quarter = case_when(event_month2 < "2016-01" ~ 1,
                             event_month2 < "2016-04" ~ 2,
                             event_month2 < "2016-07" ~ 3,
                             TRUE ~ 4)) %>%
  group_by(aor, quarter) %>%
  summarize(total = n()) %>%
  pivot_wider(names_from = quarter, values_from = total) 

colnames(encounters_2016_q) <- c("aor", "q1", "q2", "q3", "q4")

encounters_2016_q <- encounters_2016_q %>% rowwise() %>% 
  mutate(pct_change_q2 = round(((q2-q1)/q1) * 100, 2),
         pct_change_q3 = round(((q3-q2)/q2) * 100, 2),
         pct_change_q4 = round(((q4-q3)/q3) * 100, 2))

encounters_2016_q %>% kable(caption = "Quarterly % change in encounters by AOR in FY 2016")
Quarterly % change in encounters by AOR in FY 2016
aor q1 q2 q3 q4 pct_change_q2 pct_change_q3 pct_change_q4
ATL 10021 7975 8341 6890 -20.42 4.59 -17.40
BAL 1579 1652 1693 1503 4.62 2.48 -11.22
BOS 2114 2716 2093 1946 28.48 -22.94 -7.02
BUF 1614 1581 1569 1338 -2.04 -0.76 -14.72
CHI 1936 2211 2495 2804 14.20 12.84 12.38
DAL 5272 7152 8681 7799 35.66 21.38 -10.16
DEN 2339 2600 2426 2924 11.16 -6.69 20.53
DET 1782 2070 1976 2438 16.16 -4.54 23.38
ELP 838 849 810 622 1.31 -4.59 -23.21
HOU 3474 4481 4673 4484 28.99 4.28 -4.04
LOS 6189 6749 7203 6905 9.05 6.73 -4.14
MIA 3859 4437 10232 8586 14.98 130.61 -16.09
NEW 2973 2779 3118 3409 -6.53 12.20 9.33
NOL 2133 2045 2227 2323 -4.13 8.90 4.31
NYC 5328 4409 4033 8524 -17.25 -8.53 111.36
PHI 2357 2550 2647 2438 8.19 3.80 -7.90
PHO 4217 4633 4697 4672 9.86 1.38 -0.53
SEA 2847 2909 3177 3053 2.18 9.21 -3.90
SFR 5309 5204 5877 5133 -1.98 12.93 -12.66
SLC 2463 3155 3038 2981 28.10 -3.71 -1.88
SNA 3602 3461 3834 4655 -3.91 10.78 21.41
SND 2369 2369 2246 2253 0.00 -5.19 0.31
SPM 3366 3646 3876 3931 8.32 6.31 1.42
WAS 3018 3248 3524 3214 7.62 8.50 -8.80 
encounters_2016_q %>% select(1, 6:8) %>%
  pivot_longer(cols = 2:4, names_to = "Type", values_to = "Value") %>%
  ggplot(aes(x = aor, y = Value)) + geom_bar(stat = "identity") + facet_wrap(~Type) + 
  scale_y_continuous(labels = scales::percent_format(scale = 1)) +
  coord_flip() +
  labs(title = "Quarterly % change in encounters by AOR in FY 2016") +
  theme_few()

encounters_2017_q <- encounters %>% filter(fy == 2017) %>% 
  mutate(quarter = case_when(event_month2 < "2017-01" ~ 1,
                             event_month2 < "2017-04" ~ 2,
                             event_month2 < "2017-07" ~ 3,
                             TRUE ~ 4)) %>%
  group_by(aor, quarter) %>%
  summarize(total = n()) %>%
  pivot_wider(names_from = quarter, values_from = total) 

colnames(encounters_2017_q) <- c("aor", "q1", "q2", "q3", "q4")

encounters_2017_q <- encounters_2017_q %>% rowwise() %>% 
  mutate(pct_change_q2 = round(((q2-q1)/q1) * 100, 2),
         pct_change_q3 = round(((q3-q2)/q2) * 100, 2),
         pct_change_q4 = round(((q4-q3)/q3) * 100, 2))

encounters_2017_q %>% kable(caption = "Quarterly % change in encounters by AOR in FY 2017")
Quarterly % change in encounters by AOR in FY 2017
aor q1 q2 q3 q4 pct_change_q2 pct_change_q3 pct_change_q4
ATL 6022 7284 7639 7318 20.96 4.87 -4.20
BAL 1140 1235 1170 1256 8.33 -5.26 7.35
BOS 1892 2503 2553 2543 32.29 2.00 -0.39
BUF 1156 1051 1111 1020 -9.08 5.71 -8.19
CHI 2368 3746 3326 3374 58.19 -11.21 1.44
DAL 8155 8612 8008 8533 5.60 -7.01 6.56
DEN 2645 2600 2081 2333 -1.70 -19.96 12.11
DET 2185 2244 2665 2675 2.70 18.76 0.38
ELP 661 737 966 841 11.50 31.07 -12.94
HOU 4166 4843 5006 4389 16.25 3.37 -12.33
LOS 5947 8398 9904 14007 41.21 17.93 41.43
MIA 8442 8642 7404 7139 2.37 -14.33 -3.58
NEW 3718 3740 3794 3853 0.59 1.44 1.56
NOL 2303 3073 3813 4193 33.43 24.08 9.97
NYC 10104 12979 13975 12824 28.45 7.67 -8.24
PHI 2547 2703 3204 2938 6.12 18.53 -8.30
PHO 4714 4811 5005 5165 2.06 4.03 3.20
SEA 2943 3236 3318 3769 9.96 2.53 13.59
SFR 5410 5938 6801 5143 9.76 14.53 -24.38
SLC 2845 3305 3173 2924 16.17 -3.99 -7.85
SNA 4891 4338 5317 5203 -11.31 22.57 -2.14
SND 2082 2216 2560 2308 6.44 15.52 -9.84
SPM 4207 4423 4767 4301 5.13 7.78 -9.78
WAS 2816 2315 2166 2980 -17.79 -6.44 37.58 
encounters_2017_q %>% select(1, 6:8) %>%
  pivot_longer(cols = 2:4, names_to = "Type", values_to = "Value") %>%
  ggplot(aes(x = aor, y = Value)) + geom_bar(stat = "identity") + facet_wrap(~Type) + 
  scale_y_continuous(labels = scales::percent_format(scale = 1)) +
  coord_flip() +
  labs(title = "Quarterly % change in encounters by AOR in FY 2017") +
  theme_few()

encounters_2018_q <- encounters %>% filter(fy == 2018) %>% 
  mutate(quarter = case_when(event_month2 < "2018-01" ~ 1,
                             event_month2 < "2018-04" ~ 2,
                             event_month2 < "2018-07" ~ 3,
                             TRUE ~ 4)) %>%
  group_by(aor, quarter) %>%
  summarize(total = n()) %>%
  pivot_wider(names_from = quarter, values_from = total) 

colnames(encounters_2018_q) <- c("aor", "q1", "q2", "q3", "q4")

encounters_2018_q <- encounters_2018_q %>% rowwise() %>% 
  mutate(pct_change_q2 = round(((q2-q1)/q1) * 100, 2),
         pct_change_q3 = round(((q3-q2)/q2) * 100, 2),
         pct_change_q4 = round(((q4-q3)/q3) * 100, 2))

encounters_2018_q %>% kable(caption = "Quarterly % change in encounters by AOR in FY 2018")
Quarterly % change in encounters by AOR in FY 2018
aor q1 q2 q3 q4 pct_change_q2 pct_change_q3 pct_change_q4
ATL 6360 6867 7879 7770 7.97 14.74 -1.38
BAL 1046 1061 960 871 1.43 -9.52 -9.27
BOS 2260 2533 3000 2546 12.08 18.44 -15.13
BUF 973 904 820 850 -7.09 -9.29 3.66
CHI 3081 3370 3334 3466 9.38 -1.07 3.96
DAL 7735 7989 8991 9043 3.28 12.54 0.58
DEN 2509 2147 2150 1988 -14.43 0.14 -7.53
DET 2419 2494 2222 1904 3.10 -10.91 -14.31
ELP 717 804 913 845 12.13 13.56 -7.45
HOU 4441 4765 5511 4578 7.30 15.66 -16.93
LOS 12822 13151 14597 14401 2.57 11.00 -1.34
MIA 7269 12025 13492 13090 65.43 12.20 -2.98
NEW 3046 2912 2776 2241 -4.40 -4.67 -19.27
NOL 3690 4120 4385 4904 11.65 6.43 11.84
NYC 12227 10342 9977 9482 -15.42 -3.53 -4.96
PHI 2589 2518 2330 2201 -2.74 -7.47 -5.54
PHO 4783 5010 5310 5005 4.75 5.99 -5.74
SEA 3812 3718 3415 3835 -2.47 -8.15 12.30
SFR 5309 5731 4171 3849 7.95 -27.22 -7.72
SLC 2666 3009 3718 3733 12.87 23.56 0.40
SNA 5783 5940 5688 5733 2.71 -4.24 0.79
SND 3332 1865 2357 2407 -44.03 26.38 2.12
SPM 3997 4662 4861 4560 16.64 4.27 -6.19
WAS 2824 2650 2788 3476 -6.16 5.21 24.68 
encounters_2018_q %>% select(1, 6:8) %>%
  pivot_longer(cols = 2:4, names_to = "Type", values_to = "Value") %>%
  ggplot(aes(x = aor, y = Value)) + geom_bar(stat = "identity") + facet_wrap(~Type) + 
  scale_y_continuous(labels = scales::percent_format(scale = 1)) +
  coord_flip() +
  labs(title = "Quarterly % change in encounters by AOR in FY 2018") +
  theme_few()

encounters_2019_q <- encounters %>% filter(fy == 2019) %>% 
  mutate(quarter = case_when(event_month2 < "2019-01" ~ 1,
                             event_month2 < "2019-04" ~ 2,
                             event_month2 < "2019-07" ~ 3,
                             TRUE ~ 4)) %>%
  group_by(aor, quarter) %>%
  summarize(total = n()) %>%
  pivot_wider(names_from = quarter, values_from = total) 

colnames(encounters_2019_q) <- c("aor", "q1", "q2", "q3", "q4")

encounters_2019_q <- encounters_2019_q %>% rowwise() %>% 
  mutate(pct_change_q2 = round(((q2-q1)/q1) * 100, 2),
         pct_change_q3 = round(((q3-q2)/q2) * 100, 2),
         pct_change_q4 = round(((q4-q3)/q3) * 100, 2))

encounters_2019_q %>% kable(caption = "Quarterly % change in encounters by AOR in FY 2019")
Quarterly % change in encounters by AOR in FY 2019
aor q1 q2 q3 q4 pct_change_q2 pct_change_q3 pct_change_q4
ATL 7031 7328 6695 4221 4.22 -8.64 -36.95
BAL 935 1044 1209 677 11.66 15.80 -44.00
BOS 2329 2398 2249 1506 2.96 -6.21 -33.04
BUF 898 843 858 528 -6.12 1.78 -38.46
CHI 3018 3057 3239 1833 1.29 5.95 -43.41
DAL 7718 8135 8623 5453 5.40 6.00 -36.76
DEN 1995 2118 2234 1412 6.17 5.48 -36.79
DET 1592 1518 1671 1045 -4.65 10.08 -37.46
ELP 726 1012 763 501 39.39 -24.60 -34.34
HOU 4524 3941 4115 2731 -12.89 4.42 -33.63
LOS 13567 13451 14234 9882 -0.86 5.82 -30.57
MIA 13383 12383 12413 7151 -7.47 0.24 -42.39
NEW 2341 2679 2799 1909 14.44 4.48 -31.80
NOL 3652 3797 3461 2678 3.97 -8.85 -22.62
NYC 8933 9322 9001 5745 4.35 -3.44 -36.17
PHI 1768 1930 1964 1196 9.16 1.76 -39.10
PHO 4580 4423 4646 3153 -3.43 5.04 -32.14
SEA 3556 3428 3125 2473 -3.60 -8.84 -20.86
SFR 3330 3602 3683 2321 8.17 2.25 -36.98
SLC 3058 2931 2259 1641 -4.15 -22.93 -27.36
SNA 4557 5164 6657 3454 13.32 28.91 -48.11
SND 1835 1666 1498 906 -9.21 -10.08 -39.52
SPM 4518 4510 4705 2997 -0.18 4.32 -36.30
WAS 2882 2622 2457 1831 -9.02 -6.29 -25.48 
encounters_2019_q %>% select(1, 6:8) %>%
  pivot_longer(cols = 2:4, names_to = "Type", values_to = "Value") %>%
  ggplot(aes(x = aor, y = Value)) + geom_bar(stat = "identity") + facet_wrap(~Type) + 
  scale_y_continuous(labels = scales::percent_format(scale = 1)) +
  coord_flip() +
  labs(title = "Quarterly % change in encounters by AOR in FY 2019") +
  theme_few()

#encounter_landmark <- encounters %>% group_by(landmark) %>% summarize(total = n())
#write.csv(encounter_landmark, 'encounter_landmark.csv')

encounters <- encounters %>% mutate(landmark2 = 
                                case_when(grepl('NON-SPECIFIC|ARREST AT|ATD|BUREAU OF PRISONS-|FEDCAP-', landmark) ~ "Non-specific",
                                          landmark %in% c("LICENSING UNIT/STATE POLICE", "287g", "at-large", "California Healthcare Facility", "CALIFORNIA HIGHWAY PATROL", "CAP ACI", "CIS REFERRAL", "FEDERAL DETENTION CENTER (FDC)", "FIELD ARREST", "FTC CI (Federal Transfer Center)", "FTM-JCART", "FTM-VCAS", "FUG - NON FUGITIVE", "FUGITIVE OPERATIONS", "FUGITIVE SOUTH TEAM ARRESTS", "FUGOP", "FUGOPS", "STREET ARREST", "STREET ARRESTS", "U.S. Marshalls Service", "U.S. Marshals", "U.S. Marshals Service", "U.S. MARSHALS SERVICE"< "U.S. PROBATION OFFICE", "UNITED STATES MARSHALL SERVICE", "UNITED STATES PROBATION & PAROLE", "UNITED STATES PROBATION", "US 281 TO FM 493 EXP 83 NORTH TO FM 490", "US DISTRICT COURT", "US MARSHALLS", "US Marshals TF", "USCIS ARREST", "USCIS REFERRALS") ~ "Non-specific",
                                          landmark == 'DO NOT USE'  ~ "DO NOT USE",
                                          landmark == ''  ~ "NA",
                                          TRUE ~ "Specific"))
#test <- encounters %>% select(landmark, landmark2)

specificity_encounters <- encounters %>% 
  group_by(aor, landmark2) %>%
  summarize(total = n()) %>% 
  pivot_wider(names_from = landmark2, values_from = total)
  
specificity_encounters %>% kable(caption = 'Encounter landmark specificity by AOR FY2016-2019')
Encounter landmark specificity by AOR FY2016-2019
aor NA Non-specific Specific DO NOT USE
ATL 23777 40401 51463 NA
BAL 10837 1591 6603 NA
BOS 14369 4990 17822 NA
BUF 11051 908 5117 38
CHI 7538 15574 23546 NA
DAL 43930 11569 70400 NA
DEN 14049 860 21592 NA
DET 17349 5706 9287 558
ELP 3645 4088 4872 NA
HOU 8070 14942 45969 1141
LOS 22480 31691 117236 NA
MIA 100915 21514 27517 1
NEW 2308 1793 43986 NA
NOL 10231 12207 30336 23
NYC 118551 1533 27044 77
PHI 8060 8359 21461 NA
PHO 36500 5282 33042 NA
SEA 16472 712 35430 NA
SFR 25742 14799 36270 NA
SLC 16859 13392 16648 NA
SNA 21513 24095 32669 NA
SND 6524 3542 20061 4142
SPM 21295 8014 38017 1
WAS 10478 15297 19036 NA 
specificity_encounters2 <- specificity_encounters %>% 
  replace(is.na(.), 0) %>%
  rowwise() %>%
  mutate("Non-specific total" = sum(c_across("NA":"DO NOT USE"))) %>%
  select("Non-specific total", "Specific") %>%
  rowwise() %>%
  mutate("Total" =  sum(c_across("Non-specific total": "Specific"))) %>%
  rowwise() %>% mutate("%specific" = round((Specific/Total), 2))

specificity_encounters2 %>% kable(caption = 'Encounter landmark specificity % by AOR FY2016-2019')
Encounter landmark specificity % by AOR FY2016-2019
aor Non-specific total Specific Total %specific
ATL 115641 51463 167104 0.31
BAL 19031 6603 25634 0.26
BOS 37181 17822 55003 0.32
BUF 17114 5117 22231 0.23
CHI 46658 23546 70204 0.34
DAL 125899 70400 196299 0.36
DEN 36501 21592 58093 0.37
DET 32900 9287 42187 0.22
ELP 12605 4872 17477 0.28
HOU 70122 45969 116091 0.40
LOS 171407 117236 288643 0.41
MIA 149947 27517 177464 0.16
NEW 48087 43986 92073 0.48
NOL 52797 30336 83133 0.36
NYC 147205 27044 174249 0.16
PHI 37880 21461 59341 0.36
PHO 74824 33042 107866 0.31
SEA 52614 35430 88044 0.40
SFR 76811 36270 113081 0.32
SLC 46899 16648 63547 0.26
SNA 78277 32669 110946 0.29
SND 34269 20061 54330 0.37
SPM 67327 38017 105344 0.36
WAS 44811 19036 63847 0.30 
# by FY
encounters %>% 
  group_by(citizenship, fy) %>%
  summarize(total = n()) %>%
  pivot_wider(names_from = fy, values_from = total) %>%
  replace(is.na(.), 0) %>%
  mutate(total = sum(c_across('2016':'2019'))) %>%
  arrange(desc(total)) %>%
  head(n=15) %>%
  kable(caption = 'Top 15 countries whose citizens were encountered FY2016-2019')
Top 15 countries whose citizens were encountered FY2016-2019
citizenship 2016 2017 2018 2019 total
MEXICO 163214 183458 200299 165801 712772
UNKNOWN 37101 39709 30830 18511 126151
GUATEMALA 18769 24526 31151 30051 104497
HONDURAS 16959 22004 25569 24899 89431
EL SALVADOR 17772 19577 21394 19068 77811
CUBA 7889 10984 14744 14279 47896
DOMINICAN REPUBLIC 8814 13717 12533 11487 46551
JAMAICA 5317 7516 7835 7021 27689
HAITI 3719 4964 5279 5150 19112
CHINA, PEOPLES REPUBLIC OF 3669 5453 5222 4269 18613
COLOMBIA 3139 4109 4351 4080 15679
ECUADOR 2484 3889 3664 3571 13608
INDIA 2670 3316 3321 3041 12348
BRAZIL 1884 2395 2983 2761 10023
VIETNAM 2471 2449 2841 2167 9928 
# by AOR

# by FY-AOR
# by FY
encounters %>% 
  group_by(gender, fy) %>%
  summarize(total = n()) %>%
  pivot_wider(names_from = fy, values_from = total) %>%
  replace(is.na(.), 0) %>%
  mutate(total = sum(c_across('2016':'2019'))) %>%
  arrange(desc(total)) %>%
  kable(caption = 'Gender makeup among those encounters FY2016-2019')
Gender makeup among those encounters FY2016-2019
gender 2016 2017 2018 2019 total
Male 314595 371570 389314 326713 1402192
Female 38627 45858 50365 43824 178674
Unknown 1960 1654 5016 7293 15923
9 4 5 0 18 
# by AOR

# by FY-AOR

Removals

removals <- read.csv("../../export/output/removals.csv.gz", sep = "|")
removals <- removals %>% filter(aor != "HQ") %>%
  mutate(apprehension_date2 = as.Date(apprehension_date, format = "%m/%d/%Y"),
         departed_date2 = as.Date(departed_date, format = "%m/%d/%Y"),
         removal_date2 = as.Date(removal_date, format = "%m/%d/%Y"),
         removal_month = format(removal_date2, "%m"))
#sum(is.na(removals$apprehension_date2))
removals %>% select(1:9) %>% head() %>% kable(caption = "`removals` dataframe")
removals dataframe
aor apprehension_date processing_disposition_code citizenship gender rc_threat_level final_charge_section departed_date removal_date
NOL 8/26/2015 ADMDPT GUATEMALA Male (B)(7)(E) 237a2Aiii 10/1/2015 10/1/2015
DAL REINST MEXICO Male (B)(7)(E) 212a9Aii 10/1/2015 10/1/2015
DAL 6/19/2015 REINST MEXICO Male (B)(7)(E) 212a9CiII 10/1/2015 10/1/2015
SNA 8/21/2015 REINST HONDURAS Male (B)(7)(E) 212a9Aii 10/1/2015 10/1/2015
HOU 9/28/2015 REINST MEXICO Male (B)(7)(E) 212a9CiII 10/1/2015 10/1/2015
SNA 9/30/2015 REINST MEXICO Male (B)(7)(E) 212a9Aii 10/1/2015 10/1/2015 
removals <- removals %>% drop_na(apprehension_date2, departed_date2, removal_date2) %>%
  mutate(difference = removal_date2 - apprehension_date2,
         difference2 = removal_date2 - departed_date2,
         fy_removal = case_when(removal_date2 < "2016-10-01" ~ 2016,
                        removal_date2 < "2017-10-01" ~ 2017,
                        removal_date2 < "2018-10-01" ~ 2018,
                        removal_date2 < "2019-10-01" ~ 2019))

#sum(removals$difference2 != 0)

removals %>% 
  select(aor, apprehension_date, departed_date, removal_date, difference, difference2) %>% 
  arrange(difference) %>%
  head(30) %>% kable(caption = "`removals` dataframe sorted by `difference` in ascending order")
removals dataframe sorted by difference in ascending order
aor apprehension_date departed_date removal_date difference difference2
MIA 7/25/2018 10/20/2017 10/20/2017 -278 days 0 days
MIA 8/21/2019 11/21/2018 11/21/2018 -273 days 0 days
SNA 12/13/2017 3/31/2017 3/31/2017 -257 days 0 days
ELP 7/25/2018 1/8/2018 1/8/2018 -198 days 0 days
ELP 6/25/2017 1/1/2017 1/1/2017 -175 days 0 days
SNA 8/21/2019 2/28/2019 2/28/2019 -174 days 0 days
SND 7/17/2016 1/30/2016 1/30/2016 -169 days 0 days
PHO 5/8/2018 11/28/2017 11/28/2017 -161 days 0 days
ATL 8/1/2017 2/25/2017 2/25/2017 -157 days 0 days
PHO 8/5/2019 3/6/2019 3/6/2019 -152 days 0 days
SND 7/8/2016 2/9/2016 2/9/2016 -150 days 0 days
ELP 5/27/2016 1/12/2016 1/12/2016 -136 days 0 days
BUF 9/22/2017 5/16/2017 5/16/2017 -129 days 0 days
SND 7/21/2016 3/19/2016 3/19/2016 -124 days 0 days
SNA 3/11/2016 11/13/2015 11/13/2015 -119 days 0 days
HOU 3/8/2018 11/15/2017 11/15/2017 -113 days 0 days
SND 2/26/2018 11/21/2017 11/21/2017 -97 days 0 days
SNA 7/10/2019 4/9/2019 4/9/2019 -92 days 0 days
HOU 2/8/2017 11/9/2016 11/9/2016 -91 days 0 days
ELP 6/27/2016 3/29/2016 3/29/2016 -90 days 0 days
SND 8/11/2018 5/13/2018 5/13/2018 -90 days 0 days
SND 8/15/2018 5/17/2018 5/17/2018 -90 days 0 days
SND 7/13/2019 4/14/2019 4/14/2019 -90 days 0 days
SNA 2/28/2019 12/4/2018 12/4/2018 -86 days 0 days
SFR 7/19/2016 4/27/2016 4/27/2016 -83 days 0 days
SNA 5/8/2019 2/16/2019 2/16/2019 -81 days 0 days
SNA 8/19/2017 5/31/2017 5/31/2017 -80 days 0 days
PHO 9/6/2016 6/19/2016 6/19/2016 -79 days 0 days
SNA 6/28/2019 4/12/2019 4/12/2019 -77 days 0 days
SFR 7/19/2016 5/5/2016 5/5/2016 -75 days 0 days 
removals %>% 
  select(aor, apprehension_date, departed_date, removal_date, difference, difference2) %>% 
  arrange(desc(difference)) %>%
  head(30) %>% kable(caption = "`removals` dataframe sorted by `difference` in descending order")
removals dataframe sorted by difference in descending order
aor apprehension_date departed_date removal_date difference difference2
PHI 07/02/0999 4/2/2019 4/2/2019 372456 days 0 days
LOS 11/5/1972 7/9/2019 7/9/2019 17047 days 0 days
MIA 10/4/1972 5/31/2018 5/31/2018 16675 days 0 days
NEW 4/12/1970 12/1/2015 12/1/2015 16669 days 0 days
CHI 1/1/1972 2/29/2016 2/29/2016 16130 days 0 days
DET 8/25/1975 5/14/2019 5/14/2019 15968 days 0 days
NOL 9/15/1975 9/5/2017 9/5/2017 15331 days 0 days
DET 2/21/1977 10/8/2018 10/8/2018 15204 days 0 days
BUF 1/20/1976 7/27/2017 7/27/2017 15164 days 0 days
MIA 12/28/1978 1/28/2019 1/28/2019 14641 days 0 days
NOL 8/6/1978 3/29/2018 3/29/2018 14480 days 0 days
BUF 4/6/1976 11/19/2015 11/19/2015 14471 days 0 days
BAL 1/1/1977 3/21/2016 3/21/2016 14324 days 0 days
MIA 5/6/1979 6/22/2018 6/22/2018 14292 days 0 days
NOL 5/15/1980 6/12/2019 6/12/2019 14272 days 0 days
MIA 5/18/1980 5/31/2019 5/31/2019 14257 days 0 days
BUF 3/10/1980 4/28/2018 12/12/2018 14156 days 228 days
SFR 5/6/1980 12/28/2018 12/28/2018 14115 days 0 days
MIA 5/26/1980 10/26/2018 10/26/2018 14032 days 0 days
MIA 5/23/1980 8/31/2018 8/31/2018 13979 days 0 days
BAL 5/4/1980 7/13/2018 7/13/2018 13949 days 0 days
LOS 5/15/1980 7/13/2018 7/13/2018 13938 days 0 days
MIA 7/19/1980 8/31/2018 8/31/2018 13922 days 0 days
CHI 6/6/1980 7/13/2018 7/13/2018 13916 days 0 days
MIA 5/28/1980 6/7/2018 6/7/2018 13889 days 0 days
MIA 6/27/1980 6/22/2018 6/22/2018 13874 days 0 days
MIA 6/16/1980 6/7/2018 6/7/2018 13870 days 0 days
PHO 5/15/1980 3/16/2018 3/16/2018 13819 days 0 days
SFR 7/15/1979 5/9/2017 5/9/2017 13813 days 0 days
LOS 5/27/1980 3/16/2018 3/16/2018 13807 days 0 days 
#removals %>% filter(fy == 2016) %>%
  #group_by(aor) %>%
  #summarize(mean_length_stay = mean(difference))

There are 14,235 observations with empty apprehension_date. These were removed from the calculations for the difference between removal date and apprehension date.

There were no observations with empty departed_date or removal_date. There are differences between these 2 columns–17,890 rows show a non-0 difference in the number of days between departure and removal.

apprehension_date seems unreliable with the years going back to 0999 or 1972 with extremely long stays.

min(removals$removal_date2)
## [1] "2015-10-01"
max(removals$removal_date2)
## [1] "2019-08-31"
min(removals$departed_date2)
## [1] "2014-10-01"
max(removals$departed_date2)
## [1] "2019-08-31"

removal_date seems like the column that we should use as it falls into the FY 2016-2019 range as we see with the other datasets.

removals %>% group_by(aor, fy_removal) %>%
  summarize(avg_length = mean(difference2),
            sd_length = sd(difference2),
            median_length = median(difference2),
            min_length = min(difference2),
            max_length = max(difference2)) %>%
  kable(caption = 'Summary statistics of difference in length between departed and removal dates')
Summary statistics of difference in length between departed and removal dates
aor fy_removal avg_length sd_length median_length min_length max_length
ATL 2016 6.1691551 days 43.83697 0 days 0 days 643 days
ATL 2017 2.6430659 days 28.33298 0 days 0 days 667 days
ATL 2018 3.2392729 days 38.82457 0 days 0 days 709 days
ATL 2019 2.5376766 days 29.39282 0 days 0 days 622 days
BAL 2016 8.9518634 days 55.58866 0 days 0 days 585 days
BAL 2017 8.4646597 days 52.76248 0 days 0 days 508 days
BAL 2018 9.7764706 days 57.71096 0 days 0 days 551 days
BAL 2019 13.2274413 days 66.62939 0 days 0 days 648 days
BOS 2016 7.8678955 days 52.20632 0 days 0 days 558 days
BOS 2017 8.3531915 days 55.71655 0 days 0 days 639 days
BOS 2018 10.6078261 days 65.40115 0 days 0 days 663 days
BOS 2019 7.0005061 days 52.19691 0 days 0 days 633 days
BUF 2016 10.2564291 days 66.46097 0 days 0 days 653 days
BUF 2017 10.7040498 days 65.16314 0 days 0 days 685 days
BUF 2018 7.3079755 days 50.60671 0 days 0 days 577 days
BUF 2019 12.2961373 days 63.66652 0 days 0 days 610 days
CHI 2016 23.5362637 days 89.09290 0 days 0 days 706 days
CHI 2017 7.1914040 days 49.89055 0 days 0 days 687 days
CHI 2018 6.4913923 days 48.16068 0 days 0 days 711 days
CHI 2019 6.7342908 days 46.20352 0 days 0 days 611 days
DAL 2016 1.9786822 days 26.82921 0 days 0 days 585 days
DAL 2017 1.0903836 days 19.22348 0 days 0 days 588 days
DAL 2018 2.9821209 days 36.42944 0 days 0 days 691 days
DAL 2019 1.2126275 days 19.16902 0 days 0 days 594 days
DEN 2016 1.1544554 days 20.83842 0 days 0 days 437 days
DEN 2017 2.8016895 days 31.71398 0 days 0 days 598 days
DEN 2018 2.7808989 days 34.51332 0 days 0 days 701 days
DEN 2019 2.7037838 days 28.33550 0 days 0 days 673 days
DET 2016 3.2734875 days 35.60432 0 days 0 days 716 days
DET 2017 4.3569172 days 37.17539 0 days 0 days 718 days
DET 2018 2.7870936 days 35.10902 0 days 0 days 601 days
DET 2019 3.7823386 days 33.89012 0 days 0 days 633 days
ELP 2016 1.0737037 days 18.21701 0 days 0 days 598 days
ELP 2017 1.9836450 days 20.86461 0 days 0 days 659 days
ELP 2018 1.7219036 days 28.13021 0 days 0 days 721 days
ELP 2019 1.3337825 days 16.80524 0 days 0 days 523 days
HOU 2016 0.6334313 days 15.26746 0 days 0 days 620 days
HOU 2017 1.2624019 days 22.12144 0 days 0 days 596 days
HOU 2018 1.9404687 days 28.14747 0 days 0 days 652 days
HOU 2019 1.7775239 days 20.19376 0 days 0 days 676 days
LOS 2016 3.9475849 days 37.33105 0 days 0 days 664 days
LOS 2017 5.5085758 days 44.61237 0 days 0 days 642 days
LOS 2018 5.3374789 days 45.87989 0 days 0 days 699 days
LOS 2019 5.4119403 days 42.96930 0 days 0 days 639 days
MIA 2016 3.2363773 days 32.66018 0 days 0 days 693 days
MIA 2017 2.3146417 days 28.92808 0 days 0 days 568 days
MIA 2018 6.3647617 days 53.96262 0 days 0 days 690 days
MIA 2019 6.9258594 days 46.54552 0 days 0 days 655 days
NEW 2016 5.7450000 days 46.88249 0 days 0 days 644 days
NEW 2017 4.2647541 days 40.82162 0 days 0 days 694 days
NEW 2018 7.6981818 days 59.99998 0 days 0 days 708 days
NEW 2019 6.5214872 days 48.84139 0 days 0 days 541 days
NOL 2016 2.9680053 days 30.89691 0 days 0 days 666 days
NOL 2017 1.2199714 days 19.84142 0 days 0 days 594 days
NOL 2018 1.8815525 days 29.40557 0 days 0 days 722 days
NOL 2019 1.8438607 days 25.48014 0 days 0 days 607 days
NYC 2016 34.8554642 days 115.19405 0 days 0 days 719 days
NYC 2017 12.3087479 days 70.45212 0 days 0 days 703 days
NYC 2018 15.2229822 days 76.61916 0 days 0 days 662 days
NYC 2019 19.7866130 days 82.50797 0 days 0 days 610 days
PHI 2016 3.2995848 days 35.00971 0 days 0 days 631 days
PHI 2017 1.7123773 days 25.56816 0 days 0 days 709 days
PHI 2018 2.6811758 days 32.69922 0 days 0 days 705 days
PHI 2019 3.4593414 days 35.78063 0 days 0 days 584 days
PHO 2016 5.9026573 days 39.43678 0 days 0 days 614 days
PHO 2017 4.3001180 days 34.55664 0 days 0 days 704 days
PHO 2018 3.3303463 days 38.96328 0 days 0 days 674 days
PHO 2019 4.5315076 days 32.52683 0 days 0 days 593 days
SEA 2016 7.9279621 days 48.25505 0 days 0 days 651 days
SEA 2017 4.7959647 days 40.96160 0 days 0 days 628 days
SEA 2018 4.9588905 days 46.96900 0 days 0 days 650 days
SEA 2019 5.3020779 days 42.61528 0 days 0 days 588 days
SFR 2016 9.7662941 days 50.22294 0 days 0 days 596 days
SFR 2017 5.0871322 days 45.46372 0 days 0 days 673 days
SFR 2018 5.9174806 days 49.38457 0 days 0 days 657 days
SFR 2019 6.3126817 days 47.67986 0 days 0 days 638 days
SLC 2016 7.2021152 days 48.44198 0 days 0 days 493 days
SLC 2017 2.5071551 days 27.18521 0 days 0 days 516 days
SLC 2018 4.5185740 days 45.60774 0 days 0 days 646 days
SLC 2019 2.9519541 days 29.01458 0 days 0 days 566 days
SNA 2016 1.5507406 days 22.78240 0 days 0 days 654 days
SNA 2017 2.8223666 days 25.42996 0 days 0 days 638 days
SNA 2018 2.1025180 days 26.50142 0 days 0 days 629 days
SNA 2019 1.4087719 days 18.06210 0 days 0 days 675 days
SND 2016 2.9761353 days 25.90146 0 days 0 days 545 days
SND 2017 2.7772675 days 28.10484 0 days 0 days 627 days
SND 2018 1.8160692 days 22.07282 0 days 0 days 684 days
SND 2019 2.7786463 days 30.27898 0 days 0 days 646 days
SPM 2016 9.7472654 days 53.02626 0 days 0 days 468 days
SPM 2017 5.5527380 days 40.48814 0 days 0 days 556 days
SPM 2018 7.2124697 days 52.91135 0 days 0 days 615 days
SPM 2019 4.6962772 days 39.51267 0 days 0 days 647 days
WAS 2016 6.0816110 days 50.67308 0 days 0 days 684 days
WAS 2017 2.7523810 days 30.87572 0 days 0 days 532 days
WAS 2018 10.3324480 days 66.64961 0 days 0 days 686 days
WAS 2019 2.9173738 days 32.57664 0 days 0 days 577 days 
removals %>% group_by(aor, fy_removal) %>%
  summarize(count = n()) %>%
  pivot_wider(names_from = fy_removal, values_from = count) %>%
  kable(caption = 'Number of removals by FY 2016-2019 (by removal_date)')
Number of removals by FY 2016-2019 (by removal_date)
aor 2016 2017 2018 2019
ATL 5693 12316 13424 12740
BAL 644 764 935 809
BOS 1302 1880 2300 1976
BUF 1361 1605 1630 1398
CHI 2275 5235 5983 5013
DAL 11868 13841 14654 13526
DEN 1010 2486 3204 2775
DET 2033 3166 4091 3703
ELP 43200 21339 21097 18551
HOU 14633 13506 15790 17067
LOS 6563 7871 8282 7705
MIA 5322 6741 7594 8349
NEW 1800 2440 2475 2071
NOL 4532 9083 10435 11573
NYC 851 1749 2193 1987
PHI 3131 3668 3538 2976
PHO 21789 20342 26448 30929
SEA 2110 3965 4038 3850
SFR 5815 6186 5938 4471
SLC 1702 3494 3338 2789
SNA 72575 55243 62311 57137
SND 23717 20904 26809 20980
SPM 1737 2721 3304 2713
WAS 1887 2205 2259 2239 
removals %>% 
  mutate(month = month(removal_date2)) %>%
  group_by(fy_removal, month) %>%
  summarize(total = n()) %>%
  mutate(Year = factor(fy_removal)) %>%
  mutate(Month = factor(month,
                        levels = c(10:12, 1:9),
                        labels = c(month.abb[10:12], month.abb[1:9]))) %>%
  ggplot(aes(x = Month, y = total, color = Year, group = Year)) +
  geom_line() +
  geom_point() +
  scale_color_brewer(palette="Dark2") +
  theme_few() +
  theme(legend.position="bottom") +
  labs(color = "FY",
       title = "Total monthly removals FY 2016-2019",
       y = "Total Removals")

yearly_change_removals <- removals %>% 
  group_by(aor, fy_removal) %>%
  summarize(total = n()) %>%
  pivot_wider(names_from = fy_removal, values_from = total) 

colnames(yearly_change_removals) <- c("aor", "y_2016", "y_2017", "y_2018", "y_2019")

yearly_change_removals <- yearly_change_removals %>% rowwise() %>% 
  mutate(pct_change_17 = round(((y_2017-y_2016)/y_2016) * 100, 2),
         pct_change_18 = round(((y_2018-y_2017)/y_2017) * 100, 2),
         pct_change_19 = round(((y_2019-y_2018)/y_2018) * 100, 2))

yearly_change_removals %>% kable(caption = "Yearly % change in removals by AOR")
Yearly % change in removals by AOR
aor y_2016 y_2017 y_2018 y_2019 pct_change_17 pct_change_18 pct_change_19
ATL 5693 12316 13424 12740 116.34 9.00 -5.10
BAL 644 764 935 809 18.63 22.38 -13.48
BOS 1302 1880 2300 1976 44.39 22.34 -14.09
BUF 1361 1605 1630 1398 17.93 1.56 -14.23
CHI 2275 5235 5983 5013 130.11 14.29 -16.21
DAL 11868 13841 14654 13526 16.62 5.87 -7.70
DEN 1010 2486 3204 2775 146.14 28.88 -13.39
DET 2033 3166 4091 3703 55.73 29.22 -9.48
ELP 43200 21339 21097 18551 -50.60 -1.13 -12.07
HOU 14633 13506 15790 17067 -7.70 16.91 8.09
LOS 6563 7871 8282 7705 19.93 5.22 -6.97
MIA 5322 6741 7594 8349 26.66 12.65 9.94
NEW 1800 2440 2475 2071 35.56 1.43 -16.32
NOL 4532 9083 10435 11573 100.42 14.88 10.91
NYC 851 1749 2193 1987 105.52 25.39 -9.39
PHI 3131 3668 3538 2976 17.15 -3.54 -15.88
PHO 21789 20342 26448 30929 -6.64 30.02 16.94
SEA 2110 3965 4038 3850 87.91 1.84 -4.66
SFR 5815 6186 5938 4471 6.38 -4.01 -24.71
SLC 1702 3494 3338 2789 105.29 -4.46 -16.45
SNA 72575 55243 62311 57137 -23.88 12.79 -8.30
SND 23717 20904 26809 20980 -11.86 28.25 -21.74
SPM 1737 2721 3304 2713 56.65 21.43 -17.89
WAS 1887 2205 2259 2239 16.85 2.45 -0.89 
yearly_change_removals %>% select(aor, 6:8) %>%
  pivot_longer(cols = 2:4, names_to = "Type", values_to = "Value") %>%
  ggplot(aes(x = aor, y = Value)) + geom_bar(stat = "identity") + facet_wrap(~Type) + 
  scale_y_continuous(labels = scales::percent_format(scale = 1)) +
  coord_flip() +
  labs(title = "Yearly % change in removals by AOR") +
  theme_few()

# by FY
removals %>% 
  group_by(citizenship, fy_removal) %>%
  summarize(total = n()) %>%
  pivot_wider(names_from = fy_removal, values_from = total) %>%
  replace(is.na(.), 0) %>%
  mutate(total = sum(c_across('2016':'2019'))) %>%
  arrange(desc(total)) %>%
  head(n=15) %>%
  kable(caption = 'Top 15 countries whose citizens were removed FY2016-2019')
Top 15 countries whose citizens were removed FY2016-2019
citizenship 2016 2017 2018 2019 total
MEXICO 148223 126910 138987 112774 526894
GUATEMALA 33815 33327 50129 50922 168193
HONDURAS 21895 22220 28666 37247 110028
EL SALVADOR 20411 18650 15225 16594 70880
HAITI 290 5547 895 577 7309
DOMINICAN REPUBLIC 1833 1834 1616 1668 6951
BRAZIL 1083 1404 1660 1544 5691
ECUADOR 1077 1110 1188 1841 5216
NICARAGUA 785 818 857 1978 4438
COLOMBIA 1086 1024 1100 914 4124
INDIA 345 441 585 1408 2779
JAMAICA 739 715 700 591 2745
PERU 391 434 552 484 1861
CHINA, PEOPLES REPUBLIC OF 307 433 589 425 1754
CUBA 64 158 461 768 1451 
# by AOR

# by FY-AOR
# by FY
removals %>% 
  group_by(gender, fy_removal) %>%
  summarize(total = n()) %>%
  pivot_wider(names_from = fy_removal, values_from = total) %>%
  mutate(total = sum(c_across('2016':'2019'))) %>%
  arrange(desc(total)) %>%
  kable(caption = 'Gender makeup among those removed FY2016-2019')
Gender makeup among those removed FY2016-2019
gender 2016 2017 2018 2019 total
Male 214669 201245 228660 214596 859170
Female 22869 21493 23392 22716 90470
Unknown 12 12 18 15 57 
# by AOR

# by FY-AOR